215 lines
11 KiB
C#
215 lines
11 KiB
C#
|
using HRM.BO;
|
|||
|
|
using Ease.Core.DataAccess;
|
|||
|
|
using System.Data;
|
|||
|
|
using System;
|
|||
|
|
|
|||
|
|
namespace HRM.DA
|
|||
|
|
{
|
|||
|
|
internal class GradeDA
|
|||
|
|
{
|
|||
|
|
#region Constructor
|
|||
|
|
|
|||
|
|
private GradeDA()
|
|||
|
|
{
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Insert function
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, Grade item)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"INSERT INTO GRADES(GradeID, code, description,colorcode, maxbasic, minbasic, isfreetransport, hasGrossConcept, basicPercentofGross, hasPayscale, confirmMonthDuration, gradeSegmentID, CreatedBy, CreationDate, SequenceNo, Status,PAYROLLTYPEID,RequiredDays, IncrementAmount, MarketAnchor, InsuranceCoverage, IsOTApplicable, descriptioninbangla)" +
|
|||
|
|
" VALUES(%n, %s, %s,%s, %n, %n, %b, %b, %n, %b, %n, %n, %n, %d, %n, %n,%n,%n, %n, %n, %n, %n, %u)", item.ID,
|
|||
|
|
item.Code, item.Name, item.ColorCode, item.MaxBasic, item.MinBasic, item.IsTransport,
|
|||
|
|
item.HasGrossConcept, item.BasicPercentOfGross, item.HasPayscale, item.ConfirmMonthDuration,
|
|||
|
|
item.GradeSegmentID, item.CreatedBy, item.CreatedDate, item.Sequence, item.Status, item.PayrollTypeID,
|
|||
|
|
item.RequiredDays, item.Increment, item.MarketAnchor, item.InsuranceCoverage, item.IsOTApplicable, item.NameInBangla);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Update function
|
|||
|
|
|
|||
|
|
internal static void Update(TransactionContext tc, Grade item)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"UPDATE GRADES SET code=%s, description=%s,colorcode=%s, maxbasic=%n, minbasic=%n, isfreetransport=%b, hasGrossConcept=%b, basicPercentofGross=%n, hasPayscale=%b, confirmMonthDuration=%n, gradeSegmentID=%n, ModifiedBy=%n, ModifiedDate=%d, SequenceNo=%n, Status=%n,RequiredDays=%n, IncrementAmount=%n, MarketAnchor=%n, InsuranceCoverage=%n, IsOTApplicable=%n, descriptioninbangla=%u" +
|
|||
|
|
" WHERE GradeID=%n", item.Code, item.Name, item.ColorCode, item.MaxBasic, item.MinBasic,
|
|||
|
|
item.IsTransport, item.HasGrossConcept, item.BasicPercentOfGross, item.HasPayscale,
|
|||
|
|
item.ConfirmMonthDuration, item.GradeSegmentID, item.ModifiedBy, item.ModifiedDate, item.Sequence,
|
|||
|
|
item.Status, item.RequiredDays, item.Increment, item.MarketAnchor, item.InsuranceCoverage,
|
|||
|
|
item.IsOTApplicable, item.NameInBangla, item.ID);
|
|||
|
|
|
|||
|
|
//tc.ExecuteNonQuery("UPDATE IncomeTaxYearly SET ItemCode=-222,Itemid=-222 WHERE Description='L.T.A. / Medical Allowance'"); Neeed to Discuss With Boss
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Get Function
|
|||
|
|
|
|||
|
|
internal static IDataReader GetAllGrade(TransactionContext tc, int payrollTypeID, EnumStatus status,
|
|||
|
|
string code, string name)
|
|||
|
|
{
|
|||
|
|
string sqlClause = string.Empty;
|
|||
|
|
sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("PayrollTypeID = %n", payrollTypeID);
|
|||
|
|
if (EnumStatus.Active == status || EnumStatus.Inactive == status)
|
|||
|
|
{
|
|||
|
|
sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("status = %n", status);
|
|||
|
|
// return tc.ExecuteReader("SELECT * FROM AllowanceDeduction Where Status=%n AND ALLOWDEDUCTID>0 order by code", status);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (!string.IsNullOrWhiteSpace(code))
|
|||
|
|
{
|
|||
|
|
sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("Code = %s", code);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (!string.IsNullOrWhiteSpace(name))
|
|||
|
|
{
|
|||
|
|
sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("Name LIKE %s", ("%" + name + "%"));
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM GRADES %q order by description", sqlClause);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, EnumStatus status, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
if (EnumStatus.Active == status || EnumStatus.Inactive == status)
|
|||
|
|
return tc.ExecuteReader(
|
|||
|
|
"SELECT * FROM GRADES where Status=%n AND PayrollTypeID=%n order by code", status,
|
|||
|
|
payrollTypeID);
|
|||
|
|
else
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM GRADES WHERE PayrollTypeID=%n order by code",
|
|||
|
|
payrollTypeID);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, EnumStatus status)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM GRADES where Status=%n order by code", status);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, EnumStatus status, string sIDs,
|
|||
|
|
string sTargetPropertyName, int payrolltypeid)
|
|||
|
|
{
|
|||
|
|
string sCondition = "";
|
|||
|
|
sCondition = sTargetPropertyName + " IN(" + sIDs + ")";
|
|||
|
|
if (EnumStatus.Active == status || EnumStatus.Inactive == status)
|
|||
|
|
return tc.ExecuteReader(
|
|||
|
|
"SELECT * FROM GRADES where Status=%n AND PayrollTypeID=%n and GradeID=(select gradeid from employee where %q) order by code",
|
|||
|
|
status, payrolltypeid, sCondition);
|
|||
|
|
else
|
|||
|
|
return tc.ExecuteReader(
|
|||
|
|
"SELECT * FROM GRADES WHERE PayrollTypeID=%n and GradeID=(select gradeid from employee where %q) order by code",
|
|||
|
|
payrolltypeid, sCondition);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetAllowableGrades(TransactionContext tc, int nOpiItmeID, int nOpiParamID,
|
|||
|
|
int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader(
|
|||
|
|
"SELECT * FROM Grades WHERE GradeID NOT IN (SELECT DISTINCT(GradeID) FROM OpiParameterGrade, OpiParameter " +
|
|||
|
|
"WHERE OpiParameterGrade.OpiParameterID=OpiParameter.OpiParameterID " +
|
|||
|
|
"AND OpiParameterGrade.OpiItemID=%n AND OpiParameterGrade.OpiParameterID <> %n)" +
|
|||
|
|
"AND Grades.PayrollTypeID=%n", nOpiItmeID, nOpiParamID, payrollTypeID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetAddableGrades(TransactionContext tc, int nAllowDeductID, int nADParamID,
|
|||
|
|
int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
string sQuary = SQLParser.MakeSQL(
|
|||
|
|
"SELECT * FROM Grades WHERE GradeID NOT IN (SELECT DISTINCT(GradeID) FROM ADParameterGrade, ADParameterBasic " +
|
|||
|
|
"WHERE ADParameterGrade.ADParameterID=ADParameterBasic.ADParameterID " +
|
|||
|
|
"AND AllowDeductID=%n AND ADParameterGrade.ADParameterID <> %n)" +
|
|||
|
|
"AND Grades.PayrollTypeID=%n and Status=%n order by Grades.Code", nAllowDeductID, nADParamID,
|
|||
|
|
payrollTypeID, EnumStatus.Active);
|
|||
|
|
|
|||
|
|
return tc.ExecuteReader(sQuary);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetAddableGradesForOT(TransactionContext tc, int nTermID, int nTermParameterID,
|
|||
|
|
int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader(
|
|||
|
|
"SELECT * FROM Grades Where GradeID NOT IN(SELECT Distinct(GradeID) FROM TermEntityGrade, TermParameter " +
|
|||
|
|
"WHERE TermEntityGrade.TermEntityID=TermParameter.TermParameterID " +
|
|||
|
|
"AND TermID=%n AND TermEntityGrade.TermEntityID <> %n)" +
|
|||
|
|
"AND Grades.PayrollTypeID=%n", nTermID, nTermParameterID, payrollTypeID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetAddableGradesForBonus(TransactionContext tc, int nBonusID, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
if (nBonusID == 0)
|
|||
|
|
{
|
|||
|
|
string query1 = SQLParser.MakeSQL(@"SELECT * FROM Grades Where GradeID NOT IN
|
|||
|
|
(SELECT Distinct(GradeID) FROM BonusGrades,BONUSPARAMETER
|
|||
|
|
WHERE BonusGrades.BonusID=BONUSPARAMETER.BonusID AND BONUSPARAMETER.BonusID=0 AND BonusParameter.BonusID<>0)
|
|||
|
|
AND Grades.PayrollTypeID=%n", payrollTypeID);
|
|||
|
|
|
|||
|
|
return tc.ExecuteReader(query1);
|
|||
|
|
//return tc.ExecuteReader("SELECT * FROM Grades Where GradeID NOT IN " +
|
|||
|
|
// "(SELECT Distinct(GradeID) FROM BonusGrades,BONUSPARAMETER " +
|
|||
|
|
// "WHERE BonusGrades.BonusID=BONUSPARAMETER.BonusID AND BONUSPARAMETER.BonusID=0 AND BonusParameter.BonusID<>0) " +
|
|||
|
|
// "AND Grades.PayrollTypeID=%n", SystemInformation.CurrentSysInfo.PayrollTypeID.Integer);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
string query2 = SQLParser.MakeSQL(@"SELECT * FROM Grades Where GradeID NOT IN
|
|||
|
|
(SELECT Distinct(GradeID) FROM BonusGrades,BONUSPARAMETER
|
|||
|
|
WHERE BonusGrades.BonusID=BONUSPARAMETER.BonusID AND BONUSPARAMETER.BonusID=%n AND BonusParameter.BonusID<>%n)
|
|||
|
|
AND Grades.PayrollTypeID=%n", nBonusID, nBonusID, payrollTypeID);
|
|||
|
|
|
|||
|
|
//return tc.ExecuteReader("SELECT * FROM Grades Where GradeID NOT IN " +
|
|||
|
|
// "(SELECT Distinct(GradeID) FROM BonusGrades,BONUSPARAMETER " +
|
|||
|
|
// "WHERE BonusGrades.BonusID=BONUSPARAMETER.BonusID AND BONUSPARAMETER.BonusID=%n AND BonusParameter.BonusID<>%n) " +
|
|||
|
|
// "AND Grades.PayrollTypeID=%n", nBonusID.Integer, nBonusID.Integer, SystemInformation.CurrentSysInfo.PayrollTypeID.Integer);
|
|||
|
|
return tc.ExecuteReader(query2);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetAddableGradesForLoanParameter(TransactionContext tc, int LoaniD,
|
|||
|
|
int LoanParameterId, int payrolltypeid)
|
|||
|
|
{
|
|||
|
|
string sSQL = string.Format(
|
|||
|
|
"Select * from GRADES where GRADEID not in (Select Distinct GradeID from LoanGrades," +
|
|||
|
|
" LoanParameter where LoanParameter.LoanParameterID=LoanGrades.LoanParameterID AND LoanGrades.LoanID={0} " +
|
|||
|
|
" AND LoanGrades.LoanParameterID <>{1}) AND Grades.PayrollTypeID={2} Order by CODE", LoaniD,
|
|||
|
|
LoanParameterId, payrolltypeid);
|
|||
|
|
|
|||
|
|
string query = SQLParser.MakeSQL(sSQL);
|
|||
|
|
return tc.ExecuteReader(query);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetAllPayrollTypes(TransactionContext tc, EnumStatus status)
|
|||
|
|
{
|
|||
|
|
if (EnumStatus.Active == status || EnumStatus.Inactive == status)
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM GRADES where Status=%n order by code", status);
|
|||
|
|
else
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM GRADES order by code");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, int ID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM GRADES WHERE GradeID=%n", ID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, string sCode)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM GRADES WHERE Code=%s", sCode);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetByPayrollType(TransactionContext tc, string sCode, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM GRADES WHERE Code=%s and Payrolltypeid=%n", sCode, payrollTypeID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Delete function
|
|||
|
|
|
|||
|
|
internal static void Delete(TransactionContext tc, int ID)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM GRADES WHERE GradeID=%n", ID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
}
|