307 lines
16 KiB
C#
307 lines
16 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 EmployeeGradeSalaryDA
|
|||
|
|
|
|||
|
|
internal class EmployeeGradeSalaryDA
|
|||
|
|
{
|
|||
|
|
#region Constructor
|
|||
|
|
|
|||
|
|
private EmployeeGradeSalaryDA() { }
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Insert function
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, EmployeeGradeSalary item)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
string sSQL = SQLParser.MakeSQL("INSERT INTO GRADESALARYASSIGNMENT(EmpGradeSalaryID, GradeSalaryID, "
|
|||
|
|
+ " employeeID, tillDate, effectDate, basicSalary, gradeID, grossSalary,"
|
|||
|
|
+ " payScaleID, arrearInfo, gradeSalaryTypeID, CreatedBy, CreatedDate)" +
|
|||
|
|
" VALUES(%n, %n, "
|
|||
|
|
+ " %n, %d, %d, %n, %n, %n,"
|
|||
|
|
+ " %n, %n, %n, %n, %d)",
|
|||
|
|
item.ID.Integer, item.EmployeeSerial,
|
|||
|
|
item.EmployeeID.Integer, DataReader.GetNullValue(item.TillDate), item.EffectDate, item.BasicSalary, item.GradeID.Integer,
|
|||
|
|
item.GrossSalary, DataReader.GetNullValue(item.PayScaleDetailID,IDType.Integer) , item.ArrearType,
|
|||
|
|
item.GradeSalaryTypeID.Integer, item.CreatedBy.Integer, item.CreatedDate);
|
|||
|
|
tc.ExecuteNonQuery(sSQL);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Update function
|
|||
|
|
|
|||
|
|
internal static void Update(TransactionContext tc, EmployeeGradeSalary item)
|
|||
|
|
{
|
|||
|
|
string Ssql = SQLParser.MakeSQL(@"UPDATE GRADESALARYASSIGNMENT SET tillDate=%d, effectDate=%d, basicSalary=%n, gradeID=%n, grossSalary=%n, payScaleID=%n, arrearInfo=%n, gradeSalaryTypeID=%n, ModifiedBy=%n, ModifiedDate=%d" +
|
|||
|
|
" WHERE EmployeeID=%n AND GradeSalaryID=%n", item.TillDate,
|
|||
|
|
item.EffectDate, item.BasicSalary, item.GradeID.Integer, item.GrossSalary,
|
|||
|
|
DataReader.GetNullValue(item.PayScaleDetailID, IDType.Integer), item.ArrearType, item.GradeSalaryTypeID.Integer,
|
|||
|
|
item.ModifiedBy.Integer, item.ModifiedDate, item.EmployeeID.Integer, item.EmployeeSerial);
|
|||
|
|
tc.ExecuteNonQuery(Ssql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Get Function
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, int employeeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM GradeSalaryAssignment WHERE EmployeeID=%n Order by GradeSalaryID", employeeID);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetbyID(TransactionContext tc, ID empgradeSalaryID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM GradeSalaryAssignment WHERE EmpGradeSalaryID=%n ", empgradeSalaryID.Integer);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM GradeSalaryAssignment Order by GradeSalaryID");
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, ID employeeid, DateTime effectdate, EnumArrearType type)
|
|||
|
|
{
|
|||
|
|
//return tc.ExecuteReader("SELECT * FROM GRADESALARYASSIGNMENT WHERE employeeid=%n AND GradeSalaryID ="
|
|||
|
|
//+ " (SELECT MAX(GradeSalaryID) FROM GradeSalaryAssignment "
|
|||
|
|
// + "WHERE EmployeeID=%n AND effectDate<=%d AND ArrearInfo=%n ) AND ArrearInfo=%n ",
|
|||
|
|
// employeeid.Integer, employeeid.Integer, effectdate, type, type);
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM GRADESALARYASSIGNMENT WHERE employeeid=%n AND GradeSalaryID ="
|
|||
|
|
+ " (SELECT MAX(GradeSalaryID) FROM GradeSalaryAssignment "
|
|||
|
|
+ "WHERE EmployeeID=%n AND effectDate<=%d ) ",
|
|||
|
|
employeeid.Integer, employeeid.Integer, effectdate);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, DateTime effectdate, EnumArrearType type)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader(@"SELECT GRADESALARYASSIGNMENT.* FROM GRADESALARYASSIGNMENT ,
|
|||
|
|
(SELECT employeeid,MAX(GradeSalaryID)GradeSalaryID FROM GradeSalaryAssignment
|
|||
|
|
WHERE effectDate<=%d AND ArrearInfo=%n GROUP BY employeeid) tab2
|
|||
|
|
WHERE GRADESALARYASSIGNMENT.GradeSalaryID =tab2.GradeSalaryID
|
|||
|
|
AND GRADESALARYASSIGNMENT.employeeid =tab2.employeeid
|
|||
|
|
AND ArrearInfo=%n",
|
|||
|
|
effectdate, type, type);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, ID employeeid, DateTime effectdate, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
string Ssql = SQLParser.MakeSQL(@"Select * from GRADESALARYASSIGNMENT where
|
|||
|
|
EFFECTDATE= (Select MAX(EffectDate) from GRADESALARYASSIGNMENT where EMPLOYEEID=%n and EFFECTDATE<=%d)
|
|||
|
|
AND EMPLOYEEID=(Select EMPLOYEEID from Employee where EMPLOYEEID=%n and PAYROLLTYPEID=%n) ",
|
|||
|
|
employeeid.Integer, effectdate, employeeid.Integer, payrollTypeID);
|
|||
|
|
|
|||
|
|
return tc.ExecuteReader(@"Select * from GRADESALARYASSIGNMENT where
|
|||
|
|
EFFECTDATE= (Select MAX(EffectDate) from GRADESALARYASSIGNMENT where EMPLOYEEID=%n and EFFECTDATE<=%d)
|
|||
|
|
AND EMPLOYEEID=(Select EMPLOYEEID from Employee where EMPLOYEEID=%n and PAYROLLTYPEID=%n) ",
|
|||
|
|
employeeid.Integer, effectdate, employeeid.Integer, payrollTypeID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetMax(TransactionContext tc, int employeeID, EnumArrearType type)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM GradeSalaryAssignment WHERE EmployeeID=%n AND GradeSalaryID="
|
|||
|
|
+ "(SELECT MAX(GradeSalaryID) FROM GradeSalaryAssignment "
|
|||
|
|
+ "WHERE EmployeeID=%n AND ArrearInfo=%n AND TillDate IS NULL)", employeeID, employeeID, type);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
internal static IDataReader GetbyTillDate(TransactionContext tc, int employeeID, DateTime tillDatefrom, DateTime tillDateTo)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM GradeSalaryAssignment WHERE EmployeeID=%n AND ((TillDate BETWEEN %d AND %d) OR TillDate IS NULL)",
|
|||
|
|
employeeID, tillDatefrom, tillDateTo);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader GetbyEffectDate(TransactionContext tc, ID employeeID, DateTime effectDatefrom, DateTime effectDateTo)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM GradeSalaryAssignment WHERE EmployeeID=%n AND (EffectDate BETWEEN %d AND %d)",
|
|||
|
|
employeeID.Integer, effectDatefrom, effectDateTo);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetArrearItems(TransactionContext tc)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM GradeSalaryAssignment WHERE arrearInfo=%n order by effectdate", EnumArrearType.ToCalculate);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetArrearPaidItems(TransactionContext tc, ID employeeId, DateTime tilldateFrom)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM GradeSalaryAssignment WHERE EmployeeId=%n AND arrearInfo=%n "
|
|||
|
|
+" AND TillDate >=%d order by effectdate", employeeId.Integer, EnumArrearType.Paid, tilldateFrom );
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//
|
|||
|
|
internal static IDataReader GetCurrMonthSalaryItems(TransactionContext tc, DateTime nextPayProcessDate)
|
|||
|
|
{
|
|||
|
|
DateTime fromDate = GlobalFunctions.FirstDateOfMonth(nextPayProcessDate);
|
|||
|
|
//DateTime fromDate = new DateTime(2011, 3, 31); // for redisha back process
|
|||
|
|
string sSQL = SQLParser.MakeSQL("select DISTINCT * from ( "
|
|||
|
|
+ " select * from GradeSalaryAssignment where tilldate >= %d and effectdate <%d "
|
|||
|
|
+ " union "
|
|||
|
|
+ " select * from GradeSalaryAssignment where effectdate >= %d"
|
|||
|
|
+ " union "
|
|||
|
|
+ " select * from GradeSalaryAssignment where tilldate is null )"
|
|||
|
|
+ " A order by A.employeeid, A.effectDate ",
|
|||
|
|
fromDate, fromDate, fromDate);
|
|||
|
|
//string sSQL = SQLParser.MakeSQL("select DISTINCT * from ( "
|
|||
|
|
// + " select G.* from GradeSalaryAssignment G, Employee E where G.EmployeeID=E.EmployeeID and E.PayrollTypeID=%n and tilldate >= %d and effectdate <%d "
|
|||
|
|
// + " union "
|
|||
|
|
// + " select G.* from GradeSalaryAssignment G, Employee E where G.EmployeeID=E.EmployeeID and E.PayrollTypeID=%n and effectdate >= %d"
|
|||
|
|
// + " union "
|
|||
|
|
// + " select G.* from GradeSalaryAssignment G, Employee E where G.EmployeeID=E.EmployeeID and E.PayrollTypeID=%n and tilldate is null )"
|
|||
|
|
// + " A order by A.employeeid, A.effectDate ",
|
|||
|
|
// User.CurrentUser.LogInPayrollTypeID.Integer, fromDate, fromDate, User.CurrentUser.LogInPayrollTypeID.Integer, fromDate, User.CurrentUser.LogInPayrollTypeID.Integer);
|
|||
|
|
return tc.ExecuteReader(sSQL);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetEmpBasicGrade(TransactionContext tc, DateTime dEffectDate, DateTime dEffectDate2, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
DataSet oEmpBasicGrades = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string sSQL = SQLParser.MakeSQL(@"Select GSA.EMPGRADESALARYID,emp.employeeid,Emp.EMPLOYEENO,Emp.NAME,GSA.EFFECTDATE,G.DESCRIPTION as PresentGrade,GSA.BASICSALARY as PresentBasic,US.LogInID as CommitedBy,GSA.CreatedDate,GSC.NAME as ChangeType
|
|||
|
|
from GRADESALARYASSIGNMENT as GSA,GRADES as G,EMPLOYEE as Emp,USERS as US,GRADESALARYCHANGETYPE as GSC
|
|||
|
|
where GSA.EFFECTDATE between %d and %d
|
|||
|
|
AND GSA.EMPLOYEEID=Emp.EMPLOYEEID
|
|||
|
|
AND GSC.GRADESALARYCHANGETYPEID=GSA.gradesalarytypeid
|
|||
|
|
AND US.USERID=GSA.CreatedBy
|
|||
|
|
AND Emp.GRADEID=G.GRADEID AND Emp.PayrollTypeId=%n
|
|||
|
|
order by Emp.EMPLOYEENO", dEffectDate,
|
|||
|
|
dEffectDate2, payrollTypeID);
|
|||
|
|
|
|||
|
|
oEmpBasicGrades = tc.ExecuteDataSet("Select GSA.EMPGRADESALARYID,emp.employeeid,Emp.EMPLOYEENO,Emp.NAME,GSA.EFFECTDATE,G.DESCRIPTION as PresentGrade,GSA.BASICSALARY as PresentBasic,US.LogInID as CommitedBy,GSA.CreatedDate,GSC.NAME as ChangeType "
|
|||
|
|
+ " from GRADESALARYASSIGNMENT as GSA,GRADES as G,EMPLOYEE as Emp,USERS as US,GRADESALARYCHANGETYPE as GSC"
|
|||
|
|
+ " where GSA.EFFECTDATE between %d and %d"
|
|||
|
|
+ " AND GSA.EMPLOYEEID=Emp.EMPLOYEEID"
|
|||
|
|
+ " AND GSC.GRADESALARYCHANGETYPEID=GSA.gradesalarytypeid"
|
|||
|
|
+ " AND US.USERID=GSA.CreatedBy"
|
|||
|
|
+ " AND Emp.GRADEID=G.GRADEID AND Emp.PayrollTypeId=%n"
|
|||
|
|
+ " order by Emp.EMPLOYEENO", dEffectDate,
|
|||
|
|
dEffectDate2, payrollTypeID);
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
return oEmpBasicGrades;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetEmpPrvBasicGrade(TransactionContext tc, DateTime dEffectDate, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
DataSet oEmpBasicGrades = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
oEmpBasicGrades = tc.ExecuteDataSet("Select Emp.EMPLOYEENO,Emp.NAME,GSA.EFFECTDATE,G.DESCRIPTION as PreviousGrade,GSA.BASICSALARY as PreviousBasic,US.USERNAME as CommitedBy,GSA.CreatedDate,GSC.NAME as SChangeType "
|
|||
|
|
+ " from GRADESALARYASSIGNMENT as GSA,GRADES as G,EMPLOYEE as Emp,USERS as US,GRADESALARYCHANGETYPE as GSC"
|
|||
|
|
+ " where GSA.EFFECTDATE < %d"
|
|||
|
|
+ " AND GSA.EMPLOYEEID=Emp.EMPLOYEEID"
|
|||
|
|
+ " AND GSC.GRADESALARYCHANGETYPEID=GSA.gradesalarytypeid"
|
|||
|
|
+ " AND US.USERID=GSA.CreatedBy"
|
|||
|
|
+ " AND GSA.GRADEID=G.GRADEID And Emp.PayrollTypeID=%n"
|
|||
|
|
+ " order by Emp.EMPLOYEENO", GlobalFunctions.FirstDateOfMonth(dEffectDate)
|
|||
|
|
, payrollTypeID);
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
return oEmpBasicGrades;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetmultipleTilldatedemp(TransactionContext tc)
|
|||
|
|
{
|
|||
|
|
DataSet oEmpBasicGrades = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
oEmpBasicGrades = tc.ExecuteDataSet("select employeeid from GradeSalaryAssignment where tilldate is null group by employeeid having count(*)>1");
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
return oEmpBasicGrades;
|
|||
|
|
}
|
|||
|
|
internal static DataSet GetmultipleTilldatedemp(TransactionContext tc, string sEmpIDs)
|
|||
|
|
{
|
|||
|
|
DataSet oEmpBasicGrades = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
oEmpBasicGrades = tc.ExecuteDataSet("select employeeid from GradeSalaryAssignment where tilldate is null and employeeid in(%q) group by employeeid having count(*)>1",sEmpIDs);
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
return oEmpBasicGrades;
|
|||
|
|
}
|
|||
|
|
internal static DataSet GetOldGrd(TransactionContext tc, string sEmpIDs)
|
|||
|
|
{
|
|||
|
|
DataSet oEmpBasicGrades = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
oEmpBasicGrades = tc.ExecuteDataSet(@"SELECT case(type)
|
|||
|
|
when 1 then 'Joining'
|
|||
|
|
when 2 then 'Promotion'
|
|||
|
|
when 3 then 'Confirmation'
|
|||
|
|
when 4 then 'AnnualIncrement'
|
|||
|
|
when 5 then 'SalaryRevise'
|
|||
|
|
when 6 then 'GSOther'
|
|||
|
|
end gradetype,g.DESCRIPTION grade,'' payscale,gs.GROSSSALARY,gs.BASICSALARY,gs.EFFECTDATE,gs.tilldate,gs.ARREARINFO
|
|||
|
|
FROM gp_GradeSalaryAssignment gs,gp_Employee e,gp_Grades g
|
|||
|
|
where gs.EMPLOYEEID=e.EMPLOYEEID AND gs.gradeid=g.GRADEID
|
|||
|
|
AND employeeno=%s order by GradeSalaryID", sEmpIDs);
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
return oEmpBasicGrades;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Delete function
|
|||
|
|
|
|||
|
|
internal static void Delete(TransactionContext tc, ID employeeId, int employeeSerial)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM [GRADESALARYASSIGNMENT] WHERE EMPLOYEEID=%n AND GRADESALARYID= %n",
|
|||
|
|
employeeId.Integer, employeeSerial);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteOnDate(TransactionContext tc, ID employeeId, DateTime effectdate, EnumArrearType arrtype)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM GRADESALARYASSIGNMENT WHERE EMPLOYEEID=%n AND effectDate>= %d and arrearInfo=%n",
|
|||
|
|
employeeId.Integer, effectdate, arrtype);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
internal static void DeleteFrom(TransactionContext tc, int emloyeeID, DateTime fromDate)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM [GRADESALARYASSIGNMENT] WHERE EmployeeID=%n and EffectDate>=%d", emloyeeID, fromDate);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteArrearItem(TransactionContext tc, int emloyeeID)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM GRADESALARYASSIGNMENT WHERE EmployeeID=%n and arrearInfo=%n", emloyeeID, EnumArrearType.ToCalculate);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteAll(TransactionContext tc)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM GRADESALARYASSIGNMENT");
|
|||
|
|
}
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|