898 lines
59 KiB
C#
898 lines
59 KiB
C#
|
using System;
|
|||
|
|
using System.Data;
|
|||
|
|
using Ease.Core.DataAccess;
|
|||
|
|
using HRM.BO;
|
|||
|
|
|
|||
|
|
|
|||
|
|
namespace HRM.DA
|
|||
|
|
{
|
|||
|
|
public class ArrearProcessDA
|
|||
|
|
{
|
|||
|
|
#region Constructor
|
|||
|
|
|
|||
|
|
private ArrearProcessDA()
|
|||
|
|
{
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Get
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, int id)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM ArrearProcess WHERE ArrearProcessID=%n", id);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetWithPayrollType(TransactionContext tc, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM ArrearProcess where payrolltypeid=%n", payrollTypeID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, DateTime processMonth, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM ArrearProcess Where ProcessMonth=%d AND payrolltypeid=%n",
|
|||
|
|
processMonth, payrollTypeID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetDetailItems(TransactionContext tc, int nEmpID, DateTime processMonth,
|
|||
|
|
int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("select * from arrearprocessdetailitem where arrearprocessdetailid= " +
|
|||
|
|
"(select arrearprocessdetailid from arrearprocessdetail where employeeid=%n AND arrearprocessid=(select arrearprocessid from arrearprocess " +
|
|||
|
|
" where processmonth=%d and payrolltypeid=%n)) order by serialno", nEmpID,
|
|||
|
|
processMonth, payrollTypeID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetDetailItems(TransactionContext tc, string sEmpIDs, DateTime processMonth,
|
|||
|
|
int ItemType, int ItemID, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteDataSet(@"
|
|||
|
|
SELECT ad.EMPLOYEEID, adi.* from arrearprocessdetailitem adi,arrearprocessdetail ad WHERE
|
|||
|
|
adi.ARREARPROCESSDETAILID=ad.ARREARPROCESSDETAILID AND employeeid IN (%q)
|
|||
|
|
AND ITEMTYPE=%n AND ItemID=%n
|
|||
|
|
AND ad.ARREARPROCESSID IN(SELECT MAX(arrearprocessid) from arrearprocess where payrolltypeid=%n)", sEmpIDs, ItemType,
|
|||
|
|
ItemID, payrollTypeID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetDetailItems(TransactionContext tc, int arrearProcessId, int nEmpID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("select * from arrearprocessdetailitem where arrearprocessdetailid= " +
|
|||
|
|
"(select arrearprocessdetailid from arrearprocessdetail where employeeid=%n AND arrearprocessid=%n" +
|
|||
|
|
" ) order by serialno", nEmpID, arrearProcessId);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetArrearProcessDetails(TransactionContext tc, int ArrearProcessID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM ArrearProcessDetail WHERE ArrearProcessID=%n", ArrearProcessID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetDetails(TransactionContext tc, int arrearProcessId, string nEmpID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM ArrearProcessDetail WHERE ArrearProcessID=%n AND EmployeeID in (%q)",
|
|||
|
|
arrearProcessId, nEmpID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetDetails(TransactionContext tc, int arrearProcessId, int EmpID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM ArrearProcessDetail WHERE ArrearProcessID=%n AND EmployeeID = %n",
|
|||
|
|
arrearProcessId, EmpID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetArrearProcessDetails(TransactionContext tc, string sEmpID, DateTime startDate,
|
|||
|
|
DateTime endDate, int payrolltypeID)
|
|||
|
|
{
|
|||
|
|
string sql = string.Empty;
|
|||
|
|
|
|||
|
|
sql = SQLParser.MakeSQL(@"SELECT * FROM ArrearProcessDetail
|
|||
|
|
WHERE EmployeeID IN(%q)
|
|||
|
|
AND ArrearProcessID IN
|
|||
|
|
(SELECT ArrearProcessID
|
|||
|
|
FROM ArrearProcess
|
|||
|
|
WHERE ProcessMonth BETWEEN %d AND %d
|
|||
|
|
AND PayrollTypeID=%n)", sEmpID, startDate, endDate, payrolltypeID);
|
|||
|
|
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetArrearProcessDetailsByCCIDAndEmpID(TransactionContext tc, string sCCID,
|
|||
|
|
DateTime startDate, DateTime endDate, string sEmpIDs, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
DataSet rootDataset = new DataSet();
|
|||
|
|
DataSet tempdataset = new DataSet();
|
|||
|
|
string sQuery = string.Empty;
|
|||
|
|
if (sCCID == "")
|
|||
|
|
{
|
|||
|
|
sQuery = SQLParser.MakeSQL(
|
|||
|
|
@" select costcenterid,description,emp.departmentid,sum(calculatedamount) as calculatedamount from(
|
|||
|
|
select distinct tab1.employeeid,EMPCC.costcenterid,tab1.description,tab1.calculatedamount*percentage/100 as calculatedamount,percentage from (
|
|||
|
|
select distinct b.employeeid,c.description,c.calculatedamount
|
|||
|
|
from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a
|
|||
|
|
where a.arrearprocessid=b.arrearprocessid AND
|
|||
|
|
b.arrearprocessdetailid=c.arrearprocessdetailid AND
|
|||
|
|
b.ProcessMonth between %d and %d AND
|
|||
|
|
a.payrollTypeid=%n AND
|
|||
|
|
c.Itemtype=%n AND
|
|||
|
|
b.employeeid in (%q)) tab1,
|
|||
|
|
(
|
|||
|
|
select employeeid,salarymonthlyid from salarymonthly where salarymonth between %d and %d
|
|||
|
|
AND employeeid in (%q)
|
|||
|
|
) tab2,SALARYEMPCOSTCENTER EMPCC
|
|||
|
|
where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND
|
|||
|
|
tab1.employeeid= tab2.employeeid
|
|||
|
|
AND tab2.employeeid= EMPCC.employeeid)ttttt
|
|||
|
|
,employee emp
|
|||
|
|
where ttttt.employeeid=emp.employeeid
|
|||
|
|
group by costcenterid,description,emp.departmentid
|
|||
|
|
", startDate, endDate, payrollTypeID,
|
|||
|
|
(int)EnumArearProcessItemType.Gross, sEmpIDs, startDate, endDate, sEmpIDs);
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sQuery);
|
|||
|
|
|
|||
|
|
tempdataset.Tables[0].TableName = "TotalGross";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
|
|||
|
|
sQuery = SQLParser.MakeSQL(
|
|||
|
|
@" select costcenterid,description,emp.departmentid,sum(calculatedamount) as calculatedamount,itemid from(
|
|||
|
|
select distinct tab1.employeeid,EMPCC.costcenterid,tab1.description,tab1.calculatedamount*percentage/100 as calculatedamount,percentage,tab1.itemid from (
|
|||
|
|
select distinct b.employeeid,c.description,c.calculatedamount,itemid
|
|||
|
|
from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a
|
|||
|
|
where a.arrearprocessid=b.arrearprocessid AND
|
|||
|
|
b.arrearprocessdetailid=c.arrearprocessdetailid AND
|
|||
|
|
b.ProcessMonth between %d and %d AND
|
|||
|
|
a.payrollTypeid=%n AND
|
|||
|
|
c.Itemtype=%n AND
|
|||
|
|
b.employeeid in (%q)) tab1,
|
|||
|
|
(
|
|||
|
|
select employeeid,salarymonthlyid from salarymonthly where salarymonth between %d and %d
|
|||
|
|
AND employeeid in (%q)
|
|||
|
|
) tab2,SALARYEMPCOSTCENTER EMPCC
|
|||
|
|
where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND
|
|||
|
|
tab1.employeeid= tab2.employeeid
|
|||
|
|
AND tab2.employeeid= EMPCC.employeeid)ttttt
|
|||
|
|
,employee emp
|
|||
|
|
where ttttt.employeeid=emp.employeeid
|
|||
|
|
group by costcenterid,description,emp.departmentid,itemid", startDate,
|
|||
|
|
endDate, payrollTypeID, (int)EnumArearProcessItemType.OPI, sEmpIDs, startDate, endDate, sEmpIDs);
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sQuery);
|
|||
|
|
|
|||
|
|
tempdataset.Tables[0].TableName = "TotalOPI";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
|
|||
|
|
sQuery = SQLParser.MakeSQL(
|
|||
|
|
@" select costcenterid,description,emp.departmentid,sum(calculatedamount) as calculatedamount from(
|
|||
|
|
select distinct tab1.employeeid,EMPCC.costcenterid,tab1.description,tab1.calculatedamount*percentage/100 as calculatedamount,percentage from (
|
|||
|
|
select distinct b.employeeid,c.description,c.calculatedamount
|
|||
|
|
from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a
|
|||
|
|
where a.arrearprocessid=b.arrearprocessid AND
|
|||
|
|
b.arrearprocessdetailid=c.arrearprocessdetailid AND
|
|||
|
|
b.ProcessMonth between %d and %d AND
|
|||
|
|
a.payrollTypeid=%n AND
|
|||
|
|
c.Itemtype=%n AND
|
|||
|
|
b.employeeid in (%q)) tab1,
|
|||
|
|
(
|
|||
|
|
select employeeid,salarymonthlyid from salarymonthly where salarymonth between %d and %d
|
|||
|
|
AND employeeid in (%q)
|
|||
|
|
) tab2,SALARYEMPCOSTCENTER EMPCC
|
|||
|
|
where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND
|
|||
|
|
tab1.employeeid= tab2.employeeid
|
|||
|
|
AND tab2.employeeid= EMPCC.employeeid)ttttt
|
|||
|
|
,employee emp
|
|||
|
|
where ttttt.employeeid=emp.employeeid
|
|||
|
|
group by costcenterid,description,emp.departmentid", startDate,
|
|||
|
|
endDate, payrollTypeID, (int)EnumArearProcessItemType.PF, sEmpIDs, startDate, endDate, sEmpIDs);
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sQuery);
|
|||
|
|
|
|||
|
|
tempdataset.Tables[0].TableName = "TotalPF";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
|
|||
|
|
sQuery = SQLParser.MakeSQL(
|
|||
|
|
@" select costcenterid,description,emp.departmentid,sum(calculatedamount) as calculatedamount,itemid from(
|
|||
|
|
select distinct tab1.employeeid,EMPCC.costcenterid,tab1.description,tab1.calculatedamount*percentage/100 as calculatedamount,percentage,tab1.itemid from (
|
|||
|
|
select distinct b.employeeid,c.description,c.calculatedamount,itemid
|
|||
|
|
from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a
|
|||
|
|
where a.arrearprocessid=b.arrearprocessid AND
|
|||
|
|
b.arrearprocessdetailid=c.arrearprocessdetailid AND
|
|||
|
|
b.ProcessMonth between %d and %d AND
|
|||
|
|
a.payrollTypeid=%n AND
|
|||
|
|
c.Itemtype=%n AND
|
|||
|
|
b.employeeid in (%q)) tab1,
|
|||
|
|
(
|
|||
|
|
select employeeid,salarymonthlyid from salarymonthly where salarymonth between %d and %d
|
|||
|
|
AND employeeid in (%q)
|
|||
|
|
) tab2,SALARYEMPCOSTCENTER EMPCC
|
|||
|
|
where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND
|
|||
|
|
tab1.employeeid= tab2.employeeid
|
|||
|
|
AND tab2.employeeid= EMPCC.employeeid)ttttt ,employee emp
|
|||
|
|
where ttttt.employeeid=emp.employeeid
|
|||
|
|
group by costcenterid,description,emp.departmentid,itemid", startDate,
|
|||
|
|
endDate, payrollTypeID, (int)EnumArearProcessItemType.Bonus, sEmpIDs, startDate, endDate, sEmpIDs);
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sQuery);
|
|||
|
|
|
|||
|
|
tempdataset.Tables[0].TableName = "TotalBonus";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
sQuery = SQLParser.MakeSQL(
|
|||
|
|
@" select costcenterid,description,emp.departmentid,sum(calculatedamount) as calculatedamount from(
|
|||
|
|
select distinct tab1.employeeid,EMPCC.costcenterid,tab1.description,tab1.calculatedamount*percentage/100 as calculatedamount,percentage from (
|
|||
|
|
select distinct b.employeeid,c.description,c.calculatedamount
|
|||
|
|
from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a
|
|||
|
|
where a.arrearprocessid=b.arrearprocessid AND
|
|||
|
|
b.arrearprocessdetailid=c.arrearprocessdetailid AND
|
|||
|
|
b.ProcessMonth between %d and %d AND
|
|||
|
|
a.payrollTypeid=%n AND
|
|||
|
|
c.Itemtype=%n AND
|
|||
|
|
b.employeeid in (%q)) tab1,
|
|||
|
|
(
|
|||
|
|
select employeeid,salarymonthlyid from salarymonthly where salarymonth between %d and %d
|
|||
|
|
AND employeeid in (%q)
|
|||
|
|
) tab2,SALARYEMPCOSTCENTER EMPCC
|
|||
|
|
where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND
|
|||
|
|
tab1.employeeid= tab2.employeeid
|
|||
|
|
AND tab2.employeeid= EMPCC.employeeid AND EMPCC.costcenterid in(%q) )ttttt
|
|||
|
|
,employee emp
|
|||
|
|
where ttttt.employeeid=emp.employeeid
|
|||
|
|
group by costcenterid,description,emp.departmentid
|
|||
|
|
", startDate, endDate, payrollTypeID,
|
|||
|
|
(int)EnumArearProcessItemType.Gross, sEmpIDs, startDate, endDate, sEmpIDs, sCCID);
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sQuery);
|
|||
|
|
|
|||
|
|
tempdataset.Tables[0].TableName = "TotalGross";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
|
|||
|
|
sQuery = SQLParser.MakeSQL(
|
|||
|
|
@" select costcenterid,description,emp.departmentid,sum(calculatedamount) as calculatedamount,itemid from(
|
|||
|
|
select distinct tab1.employeeid,EMPCC.costcenterid,tab1.description,tab1.calculatedamount*percentage/100 as calculatedamount,percentage,tab1.itemid from (
|
|||
|
|
select distinct b.employeeid,c.description,c.calculatedamount,itemid
|
|||
|
|
from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a
|
|||
|
|
where a.arrearprocessid=b.arrearprocessid AND
|
|||
|
|
b.arrearprocessdetailid=c.arrearprocessdetailid AND
|
|||
|
|
b.ProcessMonth between %d and %d AND
|
|||
|
|
a.payrollTypeid=%n AND
|
|||
|
|
c.Itemtype=%n AND
|
|||
|
|
b.employeeid in (%q)) tab1,
|
|||
|
|
(
|
|||
|
|
select employeeid,salarymonthlyid from salarymonthly where salarymonth between %d and %d
|
|||
|
|
AND employeeid in (%q)
|
|||
|
|
) tab2,SALARYEMPCOSTCENTER EMPCC
|
|||
|
|
where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND
|
|||
|
|
tab1.employeeid= tab2.employeeid
|
|||
|
|
AND tab2.employeeid= EMPCC.employeeid AND EMPCC.costcenterid in(%q) )ttttt
|
|||
|
|
,employee emp
|
|||
|
|
where ttttt.employeeid=emp.employeeid
|
|||
|
|
group by costcenterid,description,emp.departmentid,itemid", startDate,
|
|||
|
|
endDate, payrollTypeID, (int)EnumArearProcessItemType.OPI, sEmpIDs, startDate, endDate, sEmpIDs,
|
|||
|
|
sCCID);
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sQuery);
|
|||
|
|
|
|||
|
|
tempdataset.Tables[0].TableName = "TotalOPI";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
|
|||
|
|
sQuery = SQLParser.MakeSQL(
|
|||
|
|
@" select costcenterid,description,emp.departmentid,sum(calculatedamount) as calculatedamount from(
|
|||
|
|
select distinct tab1.employeeid,EMPCC.costcenterid,tab1.description,tab1.calculatedamount*percentage/100 as calculatedamount,percentage from (
|
|||
|
|
select distinct b.employeeid,c.description,c.calculatedamount
|
|||
|
|
from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a
|
|||
|
|
where a.arrearprocessid=b.arrearprocessid AND
|
|||
|
|
b.arrearprocessdetailid=c.arrearprocessdetailid AND
|
|||
|
|
b.ProcessMonth between %d and %d AND
|
|||
|
|
a.payrollTypeid=%n AND
|
|||
|
|
c.Itemtype=%n AND
|
|||
|
|
b.employeeid in (%q)) tab1,
|
|||
|
|
(
|
|||
|
|
select employeeid,salarymonthlyid from salarymonthly where salarymonth between %d and %d
|
|||
|
|
AND employeeid in (%q)
|
|||
|
|
) tab2,SALARYEMPCOSTCENTER EMPCC
|
|||
|
|
where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND
|
|||
|
|
tab1.employeeid= tab2.employeeid
|
|||
|
|
AND tab2.employeeid= EMPCC.employeeid AND EMPCC.costcenterid in(%q) )ttttt
|
|||
|
|
,employee emp
|
|||
|
|
where ttttt.employeeid=emp.employeeid
|
|||
|
|
group by costcenterid,description,emp.departmentid", startDate,
|
|||
|
|
endDate, payrollTypeID, (int)EnumArearProcessItemType.PF, sEmpIDs, startDate, endDate, sEmpIDs,
|
|||
|
|
sCCID);
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sQuery);
|
|||
|
|
|
|||
|
|
tempdataset.Tables[0].TableName = "TotalPF";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
|
|||
|
|
sQuery = SQLParser.MakeSQL(
|
|||
|
|
@" select costcenterid,description,emp.departmentid,sum(calculatedamount) as calculatedamount,itemid from(
|
|||
|
|
select distinct tab1.employeeid,EMPCC.costcenterid,tab1.description,tab1.calculatedamount*percentage/100 as calculatedamount,percentage,tab1.itemid from (
|
|||
|
|
select distinct b.employeeid,c.description,c.calculatedamount,itemid
|
|||
|
|
from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a
|
|||
|
|
where a.arrearprocessid=b.arrearprocessid AND
|
|||
|
|
b.arrearprocessdetailid=c.arrearprocessdetailid AND
|
|||
|
|
b.ProcessMonth between %d and %d AND
|
|||
|
|
a.payrollTypeid=%n AND
|
|||
|
|
c.Itemtype=%n AND
|
|||
|
|
b.employeeid in (%q)) tab1,
|
|||
|
|
(
|
|||
|
|
select employeeid,salarymonthlyid from salarymonthly where salarymonth between %d and %d
|
|||
|
|
AND employeeid in (%q)
|
|||
|
|
) tab2,SALARYEMPCOSTCENTER EMPCC
|
|||
|
|
where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND
|
|||
|
|
tab1.employeeid= tab2.employeeid
|
|||
|
|
AND tab2.employeeid= EMPCC.employeeid AND EMPCC.costcenterid in(%q) )ttttt ,employee emp
|
|||
|
|
where ttttt.employeeid=emp.employeeid
|
|||
|
|
group by costcenterid,description,emp.departmentid,itemid", startDate,
|
|||
|
|
endDate, payrollTypeID, (int)EnumArearProcessItemType.Bonus, sEmpIDs, startDate, endDate, sEmpIDs,
|
|||
|
|
sCCID);
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sQuery);
|
|||
|
|
|
|||
|
|
tempdataset.Tables[0].TableName = "TotalBonus";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
return rootDataset;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetArrearProcessDetailsByCCIDAndEmpID2(TransactionContext tc, string sCCID,
|
|||
|
|
DateTime startDate, DateTime endDate, string sEmpIDs)
|
|||
|
|
{
|
|||
|
|
DataSet rootDataset = new DataSet();
|
|||
|
|
DataSet tempdataset = new DataSet();
|
|||
|
|
string sQuery = string.Empty;
|
|||
|
|
if (sCCID == "")
|
|||
|
|
{
|
|||
|
|
sQuery = SQLParser.MakeSQL(@"select ttttt.EmployeeID,itemtype,sum(Amount) as Amount from(
|
|||
|
|
select distinct tab1.employeeid,EMPCC.costcenterid, itemtype,tab1.calculatedamount*percentage/100 as amount from (
|
|||
|
|
select S.EmployeeID, DESCRIPTION,itemtype,CalculatedAmount from ArrearProcessDetail S,
|
|||
|
|
ArrearProcessDetailItem b where S.EMPLOYEEID in(%q) and
|
|||
|
|
S.ArrearEffectFromDate between %d and %d AND
|
|||
|
|
S.ArrearprocessDetailID=b.ArrearprocessDetailID AND b.itemtype in(2,3,5,6,8,11,13,12))tab1,
|
|||
|
|
(
|
|||
|
|
select employeeid,salarymonthlyid from salarymonthly where salarymonth between %d and %d
|
|||
|
|
AND employeeid in (%q)
|
|||
|
|
) tab2,SALARYEMPCOSTCENTER EMPCC
|
|||
|
|
where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND
|
|||
|
|
tab1.employeeid= tab2.employeeid
|
|||
|
|
AND tab2.employeeid= EMPCC.employeeid)ttttt group by ttttt.EmployeeID,itemtype
|
|||
|
|
", sEmpIDs, startDate, endDate, startDate, endDate, sEmpIDs);
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sQuery);
|
|||
|
|
|
|||
|
|
tempdataset.Tables[0].TableName = "TotalArrear";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
sQuery = SQLParser.MakeSQL(@" select ttttt.EmployeeID,itemtype,sum(Amount) as Amount from(
|
|||
|
|
select distinct tab1.employeeid,EMPCC.costcenterid, itemtype,tab1.calculatedamount*percentage/100 as amount from (
|
|||
|
|
select S.EmployeeID, DESCRIPTION,itemtype,CalculatedAmount from ArrearProcessDetail S,
|
|||
|
|
ArrearProcessDetailItem b where S.EMPLOYEEID in(%q) and
|
|||
|
|
S.ArrearEffectFromDate between %d and %d AND
|
|||
|
|
S.ArrearprocessDetailID=b.ArrearprocessDetailID AND b.itemtype in(2,3,5,6,8,11,13,12))tab1,
|
|||
|
|
(
|
|||
|
|
select employeeid,salarymonthlyid from salarymonthly where salarymonth between %d and %d
|
|||
|
|
AND employeeid in (%q)
|
|||
|
|
) tab2,SALARYEMPCOSTCENTER EMPCC
|
|||
|
|
where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND
|
|||
|
|
tab1.employeeid= tab2.employeeid
|
|||
|
|
AND empcc.costcenterid in(%q)
|
|||
|
|
AND tab2.employeeid= EMPCC.employeeid)ttttt group by ttttt.EmployeeID,itemtype
|
|||
|
|
", sEmpIDs, startDate, endDate, startDate, endDate, sEmpIDs, sCCID);
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sQuery);
|
|||
|
|
|
|||
|
|
tempdataset.Tables[0].TableName = "TotalArrear";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
return rootDataset;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetArrearProcessDetailsByCCIDAndEmpID3(TransactionContext tc, string sCCID,
|
|||
|
|
DateTime startDate, DateTime endDate, string sEmpIDs)
|
|||
|
|
{
|
|||
|
|
DataSet rootDataset = new DataSet();
|
|||
|
|
DataSet tempdataset = new DataSet();
|
|||
|
|
string sQuery = string.Empty;
|
|||
|
|
if (sCCID == "")
|
|||
|
|
{
|
|||
|
|
sQuery = SQLParser.MakeSQL(
|
|||
|
|
@" select ttttt.EmployeeID,Description,itemid,itemtype,sum(Amount) as Amount from(
|
|||
|
|
select distinct tab1.employeeid,EMPCC.costcenterid, Description,itemid,itemtype,tab1.calculatedamount*percentage/100 as amount from (
|
|||
|
|
select S.EmployeeID, DESCRIPTION,itemid,itemtype,CalculatedAmount from ArrearProcessDetail S,
|
|||
|
|
ArrearProcessDetailItem b where S.EMPLOYEEID in(%q) and
|
|||
|
|
S.ArrearEffectFromDate between %d and %d AND
|
|||
|
|
S.ArrearprocessDetailID=b.ArrearprocessDetailID )tab1,
|
|||
|
|
(
|
|||
|
|
select employeeid,salarymonthlyid from salarymonthly where salarymonth between %d and %d
|
|||
|
|
AND employeeid in (%q)
|
|||
|
|
) tab2,SALARYEMPCOSTCENTER EMPCC
|
|||
|
|
where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND
|
|||
|
|
tab1.employeeid= tab2.employeeid
|
|||
|
|
AND tab2.employeeid= EMPCC.employeeid)ttttt group by ttttt.EmployeeID,Description,itemid,itemtype
|
|||
|
|
", sEmpIDs, startDate, endDate, startDate, endDate, sEmpIDs);
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sQuery);
|
|||
|
|
|
|||
|
|
tempdataset.Tables[0].TableName = "TotalArrear";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
sQuery = SQLParser.MakeSQL(
|
|||
|
|
@" select ttttt.EmployeeID,Description,itemid,itemtype,sum(Amount) as Amount from(
|
|||
|
|
select distinct tab1.employeeid,EMPCC.costcenterid, Description,itemid,itemtype,tab1.calculatedamount*percentage/100 as amount from (
|
|||
|
|
select S.EmployeeID, DESCRIPTION,itemid,itemtype,CalculatedAmount from ArrearProcessDetail S,
|
|||
|
|
ArrearProcessDetailItem b where S.EMPLOYEEID in(%q) and
|
|||
|
|
S.ArrearEffectFromDate between %d and %d AND
|
|||
|
|
S.ArrearprocessDetailID=b.ArrearprocessDetailID )tab1,
|
|||
|
|
(
|
|||
|
|
select employeeid,salarymonthlyid from salarymonthly where salarymonth between %d and %d
|
|||
|
|
AND employeeid in (%q)
|
|||
|
|
) tab2,SALARYEMPCOSTCENTER EMPCC
|
|||
|
|
where tab2.salarymonthlyid=EMPCC.salarymonthlyid AND
|
|||
|
|
tab1.employeeid= tab2.employeeid
|
|||
|
|
AND empcc.costcenterid in(%q)
|
|||
|
|
AND tab2.employeeid= EMPCC.employeeid)ttttt group by ttttt.EmployeeID,Description,itemid,itemtype
|
|||
|
|
", sEmpIDs, startDate, endDate, startDate, endDate, sEmpIDs, sCCID);
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sQuery);
|
|||
|
|
|
|||
|
|
tempdataset.Tables[0].TableName = "TotalArrear";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
return rootDataset;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetArrPrssDetailsByCstIDEmpIDLocAndDeptWise(TransactionContext tc, string sCCID,
|
|||
|
|
DateTime startDate, DateTime endDate, string sEmpIDs)
|
|||
|
|
{
|
|||
|
|
DataSet rootDataset = new DataSet();
|
|||
|
|
DataSet tempdataset = new DataSet();
|
|||
|
|
string sQuery = string.Empty;
|
|||
|
|
if (sCCID == "")
|
|||
|
|
{
|
|||
|
|
sQuery = SQLParser.MakeSQL(
|
|||
|
|
@"select ttttt.EmployeeID,ttttt.LocationID,ttttt.DepartmentID,ttttt.ItemID,itemtype,sum(Amount) as Amount
|
|||
|
|
FROM
|
|||
|
|
(select distinct tab1.employeeid,tab1.LocationID,tab1.DepartmentID
|
|||
|
|
,EMPCC.costcenterid,tab1.ItemID,itemtype,tab1.calculatedamount*percentage/100 as amount
|
|||
|
|
from
|
|||
|
|
(select S.EmployeeID,S.LocationID,S.DepartmentID,DESCRIPTION,b.ItemID,
|
|||
|
|
itemtype,CalculatedAmount
|
|||
|
|
from ArrearProcessDetail S,ArrearProcessDetailItem b
|
|||
|
|
where S.EMPLOYEEID in(%q)
|
|||
|
|
and S.ArrearEffectFromDate between %d and %d
|
|||
|
|
AND S.ArrearprocessDetailID=b.ArrearprocessDetailID
|
|||
|
|
)tab1,
|
|||
|
|
(select employeeid,salarymonthlyid
|
|||
|
|
from salarymonthly
|
|||
|
|
where salarymonth between %d and %d
|
|||
|
|
AND employeeid in (%q))
|
|||
|
|
tab2,
|
|||
|
|
SALARYEMPCOSTCENTER EMPCC
|
|||
|
|
where tab2.salarymonthlyid=EMPCC.salarymonthlyid
|
|||
|
|
AND tab1.employeeid= tab2.employeeid
|
|||
|
|
AND tab2.employeeid= EMPCC.employeeid
|
|||
|
|
)ttttt
|
|||
|
|
group by ttttt.EmployeeID,ttttt.LocationID,ttttt.DepartmentID,ttttt.ItemID,itemtype"
|
|||
|
|
, sEmpIDs, startDate, endDate, startDate, endDate, sEmpIDs);
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sQuery);
|
|||
|
|
|
|||
|
|
tempdataset.Tables[0].TableName = "TotalArrear";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
sQuery = SQLParser.MakeSQL(
|
|||
|
|
@"select ttttt.EmployeeID,ttttt.LocationID,ttttt.DepartmentID,ttttt.ItemID,itemtype,sum(Amount) as Amount
|
|||
|
|
FROM
|
|||
|
|
(select distinct tab1.employeeid,tab1.LocationID,tab1.DepartmentID
|
|||
|
|
,EMPCC.costcenterid,tab1.ItemID,itemtype,tab1.calculatedamount*percentage/100 as amount
|
|||
|
|
from
|
|||
|
|
(select S.EmployeeID,S.LocationID,S.DepartmentID,DESCRIPTION,b.ItemID,
|
|||
|
|
itemtype,CalculatedAmount
|
|||
|
|
from ArrearProcessDetail S,ArrearProcessDetailItem b
|
|||
|
|
where S.EMPLOYEEID in(%q)
|
|||
|
|
and S.ArrearEffectFromDate between %d and %d
|
|||
|
|
AND S.ArrearprocessDetailID=b.ArrearprocessDetailID
|
|||
|
|
)tab1,
|
|||
|
|
(select employeeid,salarymonthlyid
|
|||
|
|
from salarymonthly
|
|||
|
|
where salarymonth between %d and %d
|
|||
|
|
AND employeeid in (%q))
|
|||
|
|
tab2,
|
|||
|
|
SALARYEMPCOSTCENTER EMPCC
|
|||
|
|
where tab2.salarymonthlyid=EMPCC.salarymonthlyid
|
|||
|
|
AND tab1.employeeid= tab2.employeeid
|
|||
|
|
AND empcc.costcenterid in(%q)
|
|||
|
|
AND tab2.employeeid= EMPCC.employeeid
|
|||
|
|
)ttttt
|
|||
|
|
group by ttttt.EmployeeID,ttttt.LocationID,ttttt.DepartmentID,ttttt.ItemID,itemtype
|
|||
|
|
", sEmpIDs, startDate, endDate, startDate, endDate, sEmpIDs, sCCID);
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sQuery);
|
|||
|
|
|
|||
|
|
tempdataset.Tables[0].TableName = "TotalArrear";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
return rootDataset;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetArrearProcessDetailsByCCID(TransactionContext tc, string sCCID, DateTime startDate,
|
|||
|
|
DateTime endDate, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
DataSet rootDataset = new DataSet();
|
|||
|
|
DataSet tempdataset = new DataSet();
|
|||
|
|
string sQuery = string.Empty;
|
|||
|
|
if (sCCID == "")
|
|||
|
|
{
|
|||
|
|
sQuery = SQLParser.MakeSQL(
|
|||
|
|
@" select d.costcenterid,c.description,sum(c.calculatedamount*d.percentage/100) as calculatedamount from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a,Empcostcenter d
|
|||
|
|
where a.arrearprocessid=b.arrearprocessid AND
|
|||
|
|
b.arrearprocessdetailid=c.arrearprocessdetailid AND
|
|||
|
|
b.ProcessMonth between %d and %d AND
|
|||
|
|
a.payrollTypeid=%n AND
|
|||
|
|
c.Itemtype=%n AND
|
|||
|
|
d.employeeid=d.employeeid
|
|||
|
|
group by d.costcenterid,c.description", startDate, endDate,
|
|||
|
|
payrollTypeID, (int)EnumArearProcessItemType.Gross);
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sQuery);
|
|||
|
|
|
|||
|
|
tempdataset.Tables[0].TableName = "TotalGross";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
|
|||
|
|
sQuery = SQLParser.MakeSQL(
|
|||
|
|
@" select d.costcenterid,c.description,sum(c.calculatedamount*d.percentage/100) as calculatedamount from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a,Empcostcenter d
|
|||
|
|
where a.arrearprocessid=b.arrearprocessid AND
|
|||
|
|
b.arrearprocessdetailid=c.arrearprocessdetailid AND
|
|||
|
|
b.ProcessMonth between %d and %d AND
|
|||
|
|
a.payrollTypeid=%n AND
|
|||
|
|
c.Itemtype=%n AND
|
|||
|
|
d.employeeid=d.employeeid
|
|||
|
|
group by d.costcenterid,c.description", startDate, endDate,
|
|||
|
|
payrollTypeID, (int)EnumArearProcessItemType.OPI);
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sQuery);
|
|||
|
|
|
|||
|
|
tempdataset.Tables[0].TableName = "TotalOPI";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
|
|||
|
|
sQuery = SQLParser.MakeSQL(
|
|||
|
|
@" select d.costcenterid,c.description,sum(c.calculatedamount*d.percentage/100) as calculatedamount from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a,Empcostcenter d
|
|||
|
|
where a.arrearprocessid=b.arrearprocessid AND
|
|||
|
|
b.arrearprocessdetailid=c.arrearprocessdetailid AND
|
|||
|
|
b.ProcessMonth between %d and %d AND
|
|||
|
|
a.payrollTypeid=%n AND
|
|||
|
|
c.Itemtype=%n AND
|
|||
|
|
d.employeeid=d.employeeid
|
|||
|
|
group by d.costcenterid,c.description", startDate, endDate,
|
|||
|
|
payrollTypeID, (int)EnumArearProcessItemType.PF);
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sQuery);
|
|||
|
|
|
|||
|
|
tempdataset.Tables[0].TableName = "TotalPF";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
|
|||
|
|
sQuery = SQLParser.MakeSQL(
|
|||
|
|
@" select c.itemid,d.costcenterid,c.description,sum(c.calculatedamount*d.percentage/100) as calculatedamount
|
|||
|
|
from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a,Empcostcenter d
|
|||
|
|
where a.arrearprocessid=b.arrearprocessid AND
|
|||
|
|
b.arrearprocessdetailid=c.arrearprocessdetailid AND
|
|||
|
|
b.ProcessMonth between %d and %d AND
|
|||
|
|
a.payrollTypeid=%n AND
|
|||
|
|
c.Itemtype=%n AND
|
|||
|
|
d.employeeid=d.employeeid
|
|||
|
|
group by d.costcenterid,c.description,c.itemid", startDate, endDate,
|
|||
|
|
payrollTypeID, (int)EnumArearProcessItemType.Bonus);
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sQuery);
|
|||
|
|
|
|||
|
|
tempdataset.Tables[0].TableName = "TotalBonus";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
sQuery = SQLParser.MakeSQL(
|
|||
|
|
@" select d.costcenterid,c.description,sum(c.calculatedamount*d.percentage/100) as calculatedamount from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a,Empcostcenter d
|
|||
|
|
where a.arrearprocessid=b.arrearprocessid AND
|
|||
|
|
b.arrearprocessdetailid=c.arrearprocessdetailid AND
|
|||
|
|
b.ProcessMonth between %d and %d AND
|
|||
|
|
a.payrollTypeid=%n AND
|
|||
|
|
c.Itemtype=%n AND
|
|||
|
|
d.employeeid=d.employeeid AND d.costcenterid in(%q)
|
|||
|
|
group by d.costcenterid,c.description", startDate, endDate,
|
|||
|
|
payrollTypeID, (int)EnumArearProcessItemType.Gross, sCCID);
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sQuery);
|
|||
|
|
|
|||
|
|
tempdataset.Tables[0].TableName = "TotalGross";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
|
|||
|
|
sQuery = SQLParser.MakeSQL(
|
|||
|
|
@" select d.costcenterid,c.description,sum(c.calculatedamount*d.percentage/100) as calculatedamount from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a,Empcostcenter d
|
|||
|
|
where a.arrearprocessid=b.arrearprocessid AND
|
|||
|
|
b.arrearprocessdetailid=c.arrearprocessdetailid AND
|
|||
|
|
b.ProcessMonth between %d and %d AND
|
|||
|
|
a.payrollTypeid=%n AND
|
|||
|
|
c.Itemtype=%n AND
|
|||
|
|
d.employeeid=d.employeeid AND d.costcenterid in(%q)
|
|||
|
|
group by d.costcenterid,c.description", startDate, endDate,
|
|||
|
|
payrollTypeID, (int)EnumArearProcessItemType.OPI, sCCID);
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sQuery);
|
|||
|
|
|
|||
|
|
tempdataset.Tables[0].TableName = "TotalOPI";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
|
|||
|
|
sQuery = SQLParser.MakeSQL(
|
|||
|
|
@" select d.costcenterid,c.description,sum(c.calculatedamount*d.percentage/100) as calculatedamount from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a,Empcostcenter d
|
|||
|
|
where a.arrearprocessid=b.arrearprocessid AND
|
|||
|
|
b.arrearprocessdetailid=c.arrearprocessdetailid AND
|
|||
|
|
b.ProcessMonth between %d and %d AND
|
|||
|
|
a.payrollTypeid=%n AND
|
|||
|
|
c.Itemtype=%n AND
|
|||
|
|
d.employeeid=d.employeeid AND d.costcenterid in(%q)
|
|||
|
|
group by d.costcenterid,c.description", startDate, endDate,
|
|||
|
|
payrollTypeID, (int)EnumArearProcessItemType.PF, sCCID);
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sQuery);
|
|||
|
|
|
|||
|
|
tempdataset.Tables[0].TableName = "TotalPF";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
|
|||
|
|
sQuery = SQLParser.MakeSQL(
|
|||
|
|
@" select c.itemid,d.costcenterid,c.description,sum(c.calculatedamount*d.percentage/100) as calculatedamount
|
|||
|
|
from arrearprocessdetailitem c,arrearprocessdetail b,arrearprocess a,Empcostcenter d
|
|||
|
|
where a.arrearprocessid=b.arrearprocessid AND
|
|||
|
|
b.arrearprocessdetailid=c.arrearprocessdetailid AND
|
|||
|
|
b.ProcessMonth between %d and %d AND
|
|||
|
|
a.payrollTypeid=%n AND
|
|||
|
|
c.Itemtype=%n AND
|
|||
|
|
d.employeeid=d.employeeid AND d.costcenterid in(%q)
|
|||
|
|
group by d.costcenterid,c.description,c.itemid", startDate, endDate,
|
|||
|
|
payrollTypeID, (int)EnumArearProcessItemType.Bonus, sCCID);
|
|||
|
|
tempdataset = tc.ExecuteDataSet(sQuery);
|
|||
|
|
|
|||
|
|
tempdataset.Tables[0].TableName = "TotalBonus";
|
|||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
return rootDataset;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetArrearProcessDetailItems(TransactionContext tc, int ArrearProcessDetailID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM ArrearProcessDetailItem WHERE ArrearprocessDetailID=%n",
|
|||
|
|
ArrearProcessDetailID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
internal static IDataReader GetArrearProcessDetailItemMonthlys(TransactionContext tc,
|
|||
|
|
int ArrearProcessDetailItemID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM ARRPROSDETAILITEMMONTHLY WHERE ArrearProcessDetailItemID=%n",
|
|||
|
|
ArrearProcessDetailItemID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetArrearProcessCostDists(TransactionContext tc, int ArrearProcessDetailID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM ArrearProcessCostDist WHERE ArrearprocessDetailID=%n",
|
|||
|
|
ArrearProcessDetailID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region IsExist
|
|||
|
|
|
|||
|
|
internal static bool IsExist(TransactionContext tc, DateTime dt, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
bool isExist = false;
|
|||
|
|
object obj =
|
|||
|
|
tc.ExecuteScalar("SELECT Count(*) from ArrearProcess Where ProcessMonth=%d AND PayrollTypeID=%n",
|
|||
|
|
Ease.Core.Utility.Global.DateFunctions.LastDateOfMonth(dt), payrollTypeID);
|
|||
|
|
isExist = Convert.ToInt32(obj) > 0 ? true : false;
|
|||
|
|
return isExist;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Insert
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, ArrearProcess oArrearProcess)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"INSERT INTO ArrearProcess(ArrearProcessID, ProcessDescription, ProcessMonth, ProcessDate," +
|
|||
|
|
" IsApproved, ApprovedBy, PayrollTypeID,CreatedBy,CreationDate)" +
|
|||
|
|
" VALUES(%n, %s, %d, %d, %b, %n, %n,%n,%d)",
|
|||
|
|
oArrearProcess.ID, oArrearProcess.ProcessDescription, oArrearProcess.ProcessMonth,
|
|||
|
|
oArrearProcess.ProcessDate, oArrearProcess.IsApproved,
|
|||
|
|
DataReader.GetNullValue(oArrearProcess.ApprovedBy, 0), oArrearProcess.PayrollTypeID,
|
|||
|
|
DataReader.GetNullValue(oArrearProcess.CreatedBy, 0),
|
|||
|
|
DataReader.GetNullValue(oArrearProcess.CreatedDate));
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, ArrearProcessDetail oArrearProcessDetail)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"INSERT INTO ArrearProcessDetail(ArrearProcessDetailID,ArrearProcessID, EmployeeID, ProcessMonth, DepartmentID," +
|
|||
|
|
" DesingnationID, LocationID, BranchID,AccountNo,GradeID,ScaleID,BasicSalary,GrossSalary,IncrementPercent,ArrearEffectFromDate)" +
|
|||
|
|
" VALUES(%n,%n,%n, %d,%n,%n,%n,%n,%s,%n,%n,%n,%n,%n,%d)",
|
|||
|
|
oArrearProcessDetail.ID, oArrearProcessDetail.ArrearProcessID, oArrearProcessDetail.EmployeeID,
|
|||
|
|
oArrearProcessDetail.ProcessMonth, oArrearProcessDetail.DepartmentID,
|
|||
|
|
oArrearProcessDetail.DesingnationID,
|
|||
|
|
oArrearProcessDetail.LocationID, oArrearProcessDetail.BranchID, oArrearProcessDetail.AccountNo,
|
|||
|
|
oArrearProcessDetail.GradeID, oArrearProcessDetail.ScaleID, oArrearProcessDetail.BasicSalary,
|
|||
|
|
oArrearProcessDetail.GrossSalary, oArrearProcessDetail.IncrementPercent,
|
|||
|
|
oArrearProcessDetail.ArrearEffectFromDate);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, ArrearProcessDetailItem oArrearProcessDetailItem)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"INSERT INTO ArrearProcessDetailItem(ArrearProcessDetailItemID,ArrearProcessDetailID,ItemType,ItemID, Side, Description, CalculatedAmount," +
|
|||
|
|
" ChangedAmount, SerialNo, IsUploaded)" +
|
|||
|
|
" VALUES(%n,%n,%n, %n,%n,%s,%n,%n,%n, %n)",
|
|||
|
|
oArrearProcessDetailItem.ID, oArrearProcessDetailItem.ArrearProcessDetailID,
|
|||
|
|
(int)oArrearProcessDetailItem.ItemType, oArrearProcessDetailItem.ItemID,
|
|||
|
|
(int)oArrearProcessDetailItem.Side, oArrearProcessDetailItem.Description,
|
|||
|
|
oArrearProcessDetailItem.CalculatedAmount, oArrearProcessDetailItem.ChangedAmount,
|
|||
|
|
oArrearProcessDetailItem.SeriialNo, oArrearProcessDetailItem.IsUploaded);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc,
|
|||
|
|
ArrearProcessDetailItemMonthly oArrearProcessDetailItemMonthly)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"INSERT INTO ARRPROSDETAILITEMMONTHLY(ARRPROSDETAILITEMMONTHLYID,ArrearProcessDetailItemID,ItemType,ItemID, Side, Description, CalculatedAmount," +
|
|||
|
|
" ChangedAmount, SerialNo,SalaryMonth, IsUploaded)" +
|
|||
|
|
" VALUES(%n,%n,%n, %n,%n,%s,%n,%n,%n,%d, %n)",
|
|||
|
|
oArrearProcessDetailItemMonthly.ID, oArrearProcessDetailItemMonthly.ArrearProcessDetailItemID,
|
|||
|
|
(int)oArrearProcessDetailItemMonthly.ItemType, oArrearProcessDetailItemMonthly.ItemID,
|
|||
|
|
(int)oArrearProcessDetailItemMonthly.Side, oArrearProcessDetailItemMonthly.Description,
|
|||
|
|
oArrearProcessDetailItemMonthly.CalculatedAmount, oArrearProcessDetailItemMonthly.ChangedAmount,
|
|||
|
|
oArrearProcessDetailItemMonthly.SeriialNo, oArrearProcessDetailItemMonthly.SalaryMonth,
|
|||
|
|
oArrearProcessDetailItemMonthly.IsUploaded);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void UpdateDetailItem(TransactionContext tc, ArrearProcessDetailItem oArrearProcessDetailItem)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"Update ArrearProcessDetailItem SET CalculatedAmount=%n,ChangedAmount=%n Where ArrearProcessDetailItemID=%n",
|
|||
|
|
oArrearProcessDetailItem.CalculatedAmount, oArrearProcessDetailItem.ChangedAmount,
|
|||
|
|
oArrearProcessDetailItem.ID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, ArrearProcessCostDist oArrearProcessCostDist)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"INSERT INTO ArrearProcessCostDist(ArrearProcessCostDistID,ArrearProcessDetailID,CostcenterID,InvolvementPercent, Amount)" +
|
|||
|
|
" VALUES(%n,%n,%n, %n,%n)",
|
|||
|
|
oArrearProcessCostDist.ID, oArrearProcessCostDist.ArrearProcessDetailID,
|
|||
|
|
oArrearProcessCostDist.CostcenterID, oArrearProcessCostDist.InvolvementPercent,
|
|||
|
|
oArrearProcessCostDist.Amount);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Update
|
|||
|
|
|
|||
|
|
internal static void Update(TransactionContext tc, ArrearProcess oAsset)
|
|||
|
|
{
|
|||
|
|
//string sSQL = SQLParser.MakeSQL("UPDATE Asset SET [Code]=%s,[Name]=%s,[Description]=%s," +
|
|||
|
|
// " [BuyTime]=%d,[Remarks]=%s,[IsUsing]=%n,[BuyPrice]=%n," +
|
|||
|
|
// " [ModifiedBy]=%n,[ModifiedDate]=%d WHERE [AssetID]=%n",
|
|||
|
|
// oAsset.Code, oAsset.Name,oAsset.Description,oAsset.BuyTime,
|
|||
|
|
// oAsset.Remarks, oAsset.IsUsing, oAsset.BuyPrice,
|
|||
|
|
// DataReader.GetNullValue(oAsset.ModifiedBy),
|
|||
|
|
// DataReader.GetNullValue(oAsset.ModifiedDate),
|
|||
|
|
// oAsset.ID);
|
|||
|
|
//tc.ExecuteNonQuery(sSQL);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Delete
|
|||
|
|
|
|||
|
|
internal static void Approve(TransactionContext tc, ArrearProcess oAP, int payrollTypeID,
|
|||
|
|
DateTime nextPayProcessDate)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM INCOMETAXTEMP WHERE INCOMETAXTEMP.EMPLOYEEID IN "
|
|||
|
|
+ " (SELECT AP.EMPLOYEEID FROM ArrearProcessDetail AP"
|
|||
|
|
+ " WHERE "
|
|||
|
|
+ " AP.ArrearProcessID= %n)", oAP.ID);
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery("INSERT INTO INCOMETAXTEMP(EMPLOYEEID, ITEMCODE, ITEMID, DESCRIPTION,"
|
|||
|
|
+ " THISYEARTOTAL, PREVIOUSAMOUNT, THISMONTHAMOUNT, PROJECTEDAMOUNT,"
|
|||
|
|
+ " SIDE, POSITION)"
|
|||
|
|
+ " (SELECT SALARYTEMPIT.EMPLOYEEID, SALARYTEMPIT.ITEMCODE, SALARYTEMPIT.ITEMID,"
|
|||
|
|
+ " SALARYTEMPIT.DESCRIPTION, SALARYTEMPIT.THISYEARTOTAL, "
|
|||
|
|
+ " SALARYTEMPIT.PREVIOUSAMOUNT, SALARYTEMPIT.THISMONTHAMOUNT, "
|
|||
|
|
+ " SALARYTEMPIT.PROJECTEDAMOUNT, SALARYTEMPIT.SIDE, SALARYTEMPIT.POSITION "
|
|||
|
|
+ " "
|
|||
|
|
+ " FROM SALARYTEMPIT WHERE SALARYTEMPIT.EMPLOYEEID IN "
|
|||
|
|
+ " (SELECT DISTINCT AP.EMPLOYEEID FROM ArrearProcessDetail AP "
|
|||
|
|
+ " WHERE "
|
|||
|
|
+ " AP.ArrearProcessID= %n ))", oAP.ID);
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM SALARYTEMPIT WHERE SALARYTEMPIT.EMPLOYEEID IN "
|
|||
|
|
+ " (SELECT AP.EMPLOYEEID FROM ArrearProcessDetail AP "
|
|||
|
|
+ " WHERE "
|
|||
|
|
+ " AP.ArrearProcessID= %n)", oAP.ID);
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"Update GradeSalaryAssignment Set ArrearInfo = %n Where ArrearInfo = %n AND GradeSalaryAssignment.EmployeeID IN "
|
|||
|
|
+ " (SELECT Employee.EmployeeID FROM Employee WHERE PayrollTypeID=%n)",
|
|||
|
|
EnumArrearType.Paid, EnumArrearType.ToCalculate,
|
|||
|
|
payrollTypeID);
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"Update ADParameterEmployee SET ArrearInfo=%n Where ARREARINFO=%n AND TillDate <= %d AND ADParameterEmployee.EmployeeID IN"
|
|||
|
|
+ " (SELECT Employee.EmployeeID FROM Employee WHERE Employee.PayrollTypeID=%n)", EnumArrearType.Paid,
|
|||
|
|
EnumArrearType.ToCalculate,
|
|||
|
|
nextPayProcessDate, payrollTypeID);
|
|||
|
|
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"UPDATE ArrearProcess set IsApproved=%b, ApprovedBy=%n,ModifiedBy=%n,ModifiedDate=%d WHERE ArrearProcessID=%n",
|
|||
|
|
oAP.IsApproved, oAP.ApprovedBy, DataReader.GetNullValue(oAP.ModifiedBy),
|
|||
|
|
DataReader.GetNullValue(oAP.ModifiedDate), oAP.ID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Delete(TransactionContext tc, ArrearProcess oAP)
|
|||
|
|
{
|
|||
|
|
//tc.ExecuteNonQuery("DELETE FROM SalaryTempIT WHERE EmployeeID in (Select EmployeeID from ArrearProcessDetail Where ArrearProcessID=%n)", oAP.ID);
|
|||
|
|
//tc.ExecuteNonQuery("DELETE FROM ArrearProcessCostDist WHERE ArrearprocessDetailID IN (select ArrearProcessDetailID from ArrearProcessDetail WHERE ArrearProcessID=%n)", oAP.ID);
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"DELETE FROM ARRPROSDETAILITEMMONTHLY WHERE ArrearProcessDetailItemID IN(SELECT ArrearProcessDetailItemID from ArrearProcessDetailItem WHERE ArrearprocessDetailID IN (select ArrearProcessDetailID from ArrearProcessDetail WHERE ArrearProcessID=%n))",
|
|||
|
|
oAP.ID);
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"DELETE FROM ArrearProcessDetailItem WHERE ArrearprocessDetailID IN (select ArrearProcessDetailID from ArrearProcessDetail WHERE ArrearProcessID=%n)",
|
|||
|
|
oAP.ID);
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM ArrearProcessDetail WHERE ArrearProcessID=%n", oAP.ID);
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM ArrearProcess WHERE ArrearProcessID=%n", oAP.ID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteDetailItem(TransactionContext tc, int nID)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM ArrearProcessDetailItem WHERE ArrearprocessDetailID=%n", nID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteDetailItemByDetailID(TransactionContext tc, int arrearProcessDetailID,
|
|||
|
|
enumPayrollComponentType itemtype, int itemid)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"DELETE FROM ArrearProcessDetailItem WHERE ArrearprocessDetailID=%n AND ItemType=%n AND itemID=%n",
|
|||
|
|
arrearProcessDetailID, itemtype, itemid);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteDetailItemMonthlyByDetailID(TransactionContext tc, int arrearProcessDetailID,
|
|||
|
|
enumPayrollComponentType itemtype, int itemid)
|
|||
|
|
{
|
|||
|
|
// tc.ExecuteNonQuery(@"WITH cte AS (
|
|||
|
|
// SELECT m.* FROM ARREARPROCESSDETAIL d
|
|||
|
|
// INNER JOIN ARREARPROCESSDETAILITEM a ON d.ARREARPROCESSDETAILID = a.ARREARPROCESSDETAILID
|
|||
|
|
// INNER JOIN ARRPROSDETAILITEMMONTHLY m ON a.ARREARPROCESSDETAILITEMID = m.ARREARPROCESSDETAILITEMID
|
|||
|
|
// WHERE d.ARREARPROCESSDETAILID = %n AND m.ITEMTYPE=%n AND m.ITEMID=%n
|
|||
|
|
// )
|
|||
|
|
// DELETE FROM cte
|
|||
|
|
// ", arrearProcessDetailID, itemtype, itemid);
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery(@"DELETE FROM ARRPROSDETAILITEMMONTHLY WHERE ARRPROSDETAILITEMMONTHLYID IN(
|
|||
|
|
|
|||
|
|
SELECT m.ARRPROSDETAILITEMMONTHLYID FROM ARREARPROCESSDETAIL d
|
|||
|
|
INNER JOIN ARREARPROCESSDETAILITEM a ON d.ARREARPROCESSDETAILID = a.ARREARPROCESSDETAILID
|
|||
|
|
INNER JOIN ARRPROSDETAILITEMMONTHLY m ON a.ARREARPROCESSDETAILITEMID = m.ARREARPROCESSDETAILITEMID
|
|||
|
|
WHERE d.ARREARPROCESSDETAILID = %n AND m.ITEMTYPE=%n AND m.ITEMID=%n
|
|||
|
|
)", arrearProcessDetailID, itemtype, itemid);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteTaxDetailItem(TransactionContext tc, int nID, enumPayrollComponentType type)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM ARREARPROCESSDETAILITEM WHERE ArrearprocessDetailID=%n AND ITEMTYPE", nID,
|
|||
|
|
type);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteDetailItemMonthly(TransactionContext tc, int nID)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM ARRPROSDETAILITEMMONTHLY WHERE ArrearprocessDetailItemID=%n", nID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
}
|