222 lines
12 KiB
C#
222 lines
12 KiB
C#
|
using HRM.BO;
|
|||
|
|
using Ease.Core.DataAccess;
|
|||
|
|
using System;
|
|||
|
|
using System.Data;
|
|||
|
|
using System.Net.Mail;
|
|||
|
|
using System.Security.Policy;
|
|||
|
|
using static iTextSharp.text.pdf.AcroFields;
|
|||
|
|
|
|||
|
|
|
|||
|
|
namespace HRM.DA
|
|||
|
|
{
|
|||
|
|
#region DataIntergretion
|
|||
|
|
|
|||
|
|
public class DataIntegrationRequestDA
|
|||
|
|
{
|
|||
|
|
#region Constructor
|
|||
|
|
|
|||
|
|
public DataIntegrationRequestDA()
|
|||
|
|
{
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Insert function
|
|||
|
|
|
|||
|
|
public static void Insert(TransactionContext tc, DataIntegration oItem)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"INSERT INTO DataIntegrationRequest(BatchID, UploadDate, EmployeeNo,GlobalID,PreviousEmployeeNo,ContractChangeEndDate,EmployeeName,FathersName, Designation, DateOfBirth, DateOfJoining,ConfirmationDate,TerminationDate,Category, Religion, MaritalStatus,Gender,TIN,IsForeignExpatriate,Grade,BasicSalary, SpecialAllowance, BasicSalaryEffectDate, SalaryChangeType, DivisionCode, Division, DepartmentCode, Department, SectionCode," +
|
|||
|
|
"Section,SubSectionCode,SubSection,UnitCode,Unit,SubUnitCode,SubUnit,ZoneCode,Zone,LocationCode,Location,CostCenterCode,AssignmentChangeDate,SalaryBankCode,SalaryBank,SalaryBranchCode,SalaryBranch,SalaryAccountNumber,SalaryAccountEffectDate,EmailAddress,MobileNo,CompanyCode,Company)" +
|
|||
|
|
" VALUES(%n, %s, %s,%s,%s,%s,%s, %s, %s, %s, %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s, %s, %s, %s, %s, %s, %s, %s, %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", oItem.BatchID, oItem.UploadDate,
|
|||
|
|
oItem.EmployeeNo, oItem.GlobalID, oItem.PreviousEmployeeNo, oItem.ContractChangeEndDate,
|
|||
|
|
oItem.EmployeeName, oItem.FathersName,oItem.Designation,oItem.DateOfBirth,oItem.DateOfJoining,oItem.ConfirmationDate,oItem.TerminationDate,oItem.Category,oItem.Religion,oItem.MaritalStatus,oItem.Gender,oItem.TIN,oItem.IsForeignExpatriate,oItem.Grade,oItem.BasicSalary,oItem.SpecialAllowance,oItem.BasicSalary, oItem.SpecialAllowance,oItem.BasicSalaryEffectDate,oItem.SALARY_CHANGE_TYPE,oItem.DivisionCode,oItem.Division,oItem.DepartmentCode,
|
|||
|
|
oItem.Department,oItem.SectionCode,oItem.Section,oItem.SubSectionCode,oItem.SubSection,oItem.UnitCode,oItem.Unit,oItem.SubUnitCode, oItem.SubUnit, oItem.ZoneCode, oItem.Zone, oItem.LocationCode, oItem.Location, oItem.CostCenterCode, oItem.AssignmentChangeDate, oItem.SalaryBankCode, oItem.SalaryBank, oItem.SalaryBranchCode, oItem.SalaryBranch, oItem.SalaryAccountNumber,
|
|||
|
|
oItem.SALARY_ACCOUNT_EFFECT_DATE, oItem.EmailAddress, oItem.MobileNo, oItem.CompanyCode, oItem.Company);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static void InsertErrorList(TransactionContext tc, SuccessorErrorList oItem)
|
|||
|
|
{
|
|||
|
|
//tc.ExecuteNonQuery(
|
|||
|
|
// "INSERT INTO DataIntegrationRequest(BatchID, UploadDate, EmployeeNo,GlobalID,PreviousEmployeeNo,ContractChangeEndDate,EmployeeName,FathersName, Designation, DateOfBirth, DateOfJoining,ConfirmationDate,TerminationDate,Category, Religion, MaritalStatus,Gender,TIN,IsForeignExpatriate,Grade,BasicSalary, SpecialAllowance, BasicSalaryEffectDate, SalaryChangeType, DivisionCode, Division, DepartmentCode, Department, SectionCode," +
|
|||
|
|
// "Section,SubSectionCode,SubSection,UnitCode,Unit,SubUnitCode,SubUnit,ZoneCode,Zone,LocationCode,Location,CostCenterCode,AssignmentChangeDate,SalaryBankCode,SalaryBank,SalaryBranchCode,SalaryBranch,SalaryAccountNumber,SalaryAccountEffectDate,EmailAddress,MobileNo,CompanyCode,Company)" +
|
|||
|
|
// " VALUES(%n, %s, %s,%s,%s,%s,%s, %s, %s, %s, %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s, %s, %s, %s, %s, %s, %s, %s, %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", oItem.BatchID, oItem.UploadDate,
|
|||
|
|
// oItem.EmployeeNo, oItem.GlobalID, oItem.PreviousEmployeeNo, oItem.ContractChangeEndDate,
|
|||
|
|
// oItem.EmployeeName, oItem.FathersName, oItem.Designation, oItem.DateOfBirth, oItem.DateOfJoining, oItem.ConfirmationDate, oItem.TerminationDate, oItem.Category, oItem.Religion, oItem.MaritalStatus, oItem.Gender, oItem.TIN, oItem.IsForeignExpatriate, oItem.Grade, oItem.BasicSalary, oItem.SpecialAllowance, oItem.BasicSalary, oItem.SpecialAllowance, oItem.BasicSalaryEffectDate, oItem.SALARY_CHANGE_TYPE, oItem.DivisionCode, oItem.Division, oItem.DepartmentCode,
|
|||
|
|
// oItem.Department, oItem.SectionCode, oItem.Section, oItem.SubSectionCode, oItem.SubSection, oItem.UnitCode, oItem.Unit, oItem.SubUnitCode, oItem.SubUnit, oItem.ZoneCode, oItem.Zone, oItem.LocationCode, oItem.Location, oItem.CostCenterCode, oItem.AssignmentChangeDate, oItem.SalaryBankCode, oItem.SalaryBank, oItem.SalaryBranchCode, oItem.SalaryBranch, oItem.SalaryAccountNumber,
|
|||
|
|
// oItem.SALARY_ACCOUNT_EFFECT_DATE, oItem.EmailAddress, oItem.MobileNo, oItem.CompanyCode, oItem.Company);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Update function
|
|||
|
|
|
|||
|
|
//public static void Update(TransactionContext tc, Leave oItem)
|
|||
|
|
//{
|
|||
|
|
// tc.ExecuteNonQuery(
|
|||
|
|
// "UPDATE Leave SET Code=%s, Description=%s,RemarksNeeded=%b,ApplicableFor=%n,IsBalanceCalRequired=%b,AutoLeaveReason=%b, " +
|
|||
|
|
// "ModifiedBy=%n, ModifiedDate=%d, SequenceNo=%n, Status=%n,IsEarnedLeave=%b,IsHalfDayLeave=%b,IsCompensatoryLeave=%b, " +
|
|||
|
|
// "IsAttachmentNeeded=%b, AttachmentMaxDays=%n,IsLFA=%b,LfaMaxDays=%n,AllowNegativeBalance=%b,MaxConsequentDays=%n, " +
|
|||
|
|
// "IsPreApproval=%b, PreApprovalDays=%n, IsSelfCancellation=%b, SelfCancellationDays=%n, CancelOnApprovalLeaveEntry=%n, HasMinimumDays = %n, MinimumDays = %n, DescriptionInBangla=%u" +
|
|||
|
|
// " WHERE LeaveID=%n", oItem.Code, oItem.Description, oItem.RemarksNeeded, oItem.ApplicableFor,
|
|||
|
|
// oItem.IsBalanceCalculationNeeded, oItem.AutoLeaveReason, oItem.ModifiedBy, oItem.ModifiedDate,
|
|||
|
|
// oItem.Sequence, oItem.Status, oItem.IsEarnedLeave, oItem.IsHalfDayLeave, oItem.IsCompensatoryLeave,
|
|||
|
|
// oItem.IsAttachmentNeeded, oItem.AttachmentMaxDays, oItem.IsLFA, oItem.LfaMaxDays, oItem.AllowNegativeBalance,
|
|||
|
|
// oItem.MaxConsequentDays, oItem.IsPreApproval, oItem.PreApprovalDays, oItem.IsSelfCancellation, oItem.SelfCancellationDays,
|
|||
|
|
// oItem.CancelOnApprovalLeaveEntry, oItem.HasMinimumDays, oItem.MinimumDays, oItem.DescriptionInBangla,
|
|||
|
|
// oItem.ID);
|
|||
|
|
//}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region ID Generation function
|
|||
|
|
|
|||
|
|
//public static int GetNewID(TransactionContext tc)
|
|||
|
|
//{
|
|||
|
|
// return tc.GenerateID("Leave", "LeaveID");
|
|||
|
|
//}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Get Function
|
|||
|
|
|
|||
|
|
internal static DataSet GetBudgetData(TransactionContext tc, int payrolltypeid)
|
|||
|
|
{
|
|||
|
|
DataSet ds = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"select * from
|
|||
|
|
(select distinct batchID, MAX(DataIntegrationRequestID) rowNo
|
|||
|
|
from DataIntegrationRequest where payrolltypeid =%n
|
|||
|
|
group by batchID) T
|
|||
|
|
order by t.rowNo desc", payrolltypeid);
|
|||
|
|
ds = tc.ExecuteDataSet(sql);
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return ds;
|
|||
|
|
}
|
|||
|
|
internal static DataSet GetBudgetDataAllowance(TransactionContext tc, int payrolltypeid)
|
|||
|
|
{
|
|||
|
|
DataSet ds = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"select * from
|
|||
|
|
(select distinct batchID, MAX(ID) rowNo
|
|||
|
|
from DIBenefitAndDeduction where payrolltypeid =%n
|
|||
|
|
group by batchID) T
|
|||
|
|
order by t.rowNo desc", payrolltypeid);
|
|||
|
|
ds = tc.ExecuteDataSet(sql);
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return ds;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetSuccessListbyBatchID(TransactionContext tc,string batchID)
|
|||
|
|
{
|
|||
|
|
DataSet ds = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("select * from SuccessorErrorList where BatchID=%s", batchID);
|
|||
|
|
ds = tc.ExecuteDataSet(sql);
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return ds;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//internal static IDataReader Get(TransactionContext tc, EnumStatus status, string code, string name,
|
|||
|
|
// int payrollTypeID)
|
|||
|
|
//{
|
|||
|
|
// 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);
|
|||
|
|
// }
|
|||
|
|
|
|||
|
|
// if (!string.IsNullOrWhiteSpace(code))
|
|||
|
|
// {
|
|||
|
|
// sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("Code = %s", code);
|
|||
|
|
// }
|
|||
|
|
|
|||
|
|
// if (!string.IsNullOrWhiteSpace(name))
|
|||
|
|
// {
|
|||
|
|
// sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("Description LIKE %s", ("%" + name + "%"));
|
|||
|
|
// }
|
|||
|
|
|
|||
|
|
// return tc.ExecuteReader("SELECT * FROM Leave %q order by Description", sqlClause);
|
|||
|
|
//}
|
|||
|
|
|
|||
|
|
//public static IDataReader Get(TransactionContext tc)
|
|||
|
|
//{
|
|||
|
|
// return tc.ExecuteReader("SELECT * FROM Leave ORDER BY LEAVEID");
|
|||
|
|
//}
|
|||
|
|
|
|||
|
|
|
|||
|
|
// public static IDataReader Get(TransactionContext tc, string InParameterSQL, int payrollTypeID)
|
|||
|
|
// {
|
|||
|
|
// return tc.ExecuteReader("SELECT * FROM Leave Where LeaveID IN (Select "
|
|||
|
|
// + " LeaveID From LeavePARAMETER where LEAVEPARAMID IN (%q) ) AND PayrollTypeID=%n ORDER BY LEAVEID",
|
|||
|
|
// InParameterSQL, payrollTypeID);
|
|||
|
|
// }
|
|||
|
|
|
|||
|
|
|
|||
|
|
// public static IDataReader GetEmpApplicableLeave(TransactionContext tc, int empid)
|
|||
|
|
// {
|
|||
|
|
// return tc.ExecuteReader(@"select l.* from LEAVE l , LEAVEPARAMETER lp ,LeaveParameterItem lim, EMPLOYEE e where
|
|||
|
|
//lim.LEAVEPARAMID =lp.LEAVEPARAMID and lim.ItemID = e.GRADEID and e.EMPLOYEEID =%n
|
|||
|
|
//and l.LEAVEID = lp.LEAVEID and (l.APPLICABLEFOR=0 OR e.GENDER = l.APPLICABLEFOR ) order by l.DESCRIPTION",
|
|||
|
|
// empid);
|
|||
|
|
// }
|
|||
|
|
|
|||
|
|
// public static IDataReader Get(TransactionContext tc, int nLeaveID)
|
|||
|
|
// {
|
|||
|
|
// return tc.ExecuteReader("SELECT * FROM Leave WHERE LeaveID=%n", nLeaveID);
|
|||
|
|
// }
|
|||
|
|
|
|||
|
|
// public static IDataReader GetAll(TransactionContext tc)
|
|||
|
|
// {
|
|||
|
|
// return tc.ExecuteReader("SELECT * FROM Leave");
|
|||
|
|
// }
|
|||
|
|
|
|||
|
|
// public static IDataReader GetIDByName(TransactionContext tc, string sLeave, int payrollTypeID)
|
|||
|
|
// {
|
|||
|
|
// return tc.ExecuteReader("SELECT * FROM Leave WHERE Description=%s AND PayrollTypeID=%n", sLeave,
|
|||
|
|
// payrollTypeID);
|
|||
|
|
// }
|
|||
|
|
|
|||
|
|
// public static IDataReader GetIDByCode(TransactionContext tc, string sLeave, int payrollTypeID)
|
|||
|
|
// {
|
|||
|
|
// return tc.ExecuteReader("SELECT * FROM Leave WHERE CODE=%s AND PayrollTypeID=%n", sLeave, payrollTypeID);
|
|||
|
|
// }
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Delete function
|
|||
|
|
|
|||
|
|
//public static void Delete(TransactionContext tc, int nLeaveID, int payrollTypeID)
|
|||
|
|
//{
|
|||
|
|
// tc.ExecuteNonQuery("DELETE FROM Leave WHERE LeaveID=%n AND PayrollTypeID=%n", nLeaveID, payrollTypeID);
|
|||
|
|
//}
|
|||
|
|
|
|||
|
|
//internal static IDataReader GetLeaves(TransactionContext tc, int payrollTypeID)
|
|||
|
|
//{
|
|||
|
|
// return tc.ExecuteReader("SELECT * FROM Leave WHERE PayrollTypeID=%n", payrollTypeID);
|
|||
|
|
//}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|