828 lines
34 KiB
C#
828 lines
34 KiB
C#
|
using System;
|
|||
|
|
using System.Data;
|
|||
|
|
using Ease.Core.Model;
|
|||
|
|
using Ease.Core.DataAccess;
|
|||
|
|
using Ease.Core;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using Ease.Core.Utility;
|
|||
|
|
using System.IO;
|
|||
|
|
using System.Data.SqlClient;
|
|||
|
|
using HRM.BO;
|
|||
|
|
using static HRM.BO.SearchManager;
|
|||
|
|
|
|||
|
|
namespace HRM.DA
|
|||
|
|
{
|
|||
|
|
#region SearchEmployee Service
|
|||
|
|
|
|||
|
|
public class SearchEmployeeService : ServiceTemplate, ISearchEmployeeService
|
|||
|
|
{
|
|||
|
|
private int _serial;
|
|||
|
|
|
|||
|
|
public SearchEmployeeService()
|
|||
|
|
{
|
|||
|
|
_serial = 0;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private void MapObject(SearchEmployee oSearchEmployee, DataReader oReader)
|
|||
|
|
{
|
|||
|
|
oSearchEmployee.EmployeeID = oReader.GetInt32("EmployeeID").Value;
|
|||
|
|
_serial = _serial + 1;
|
|||
|
|
base.SetObjectID(oSearchEmployee, (oSearchEmployee.EmployeeID));
|
|||
|
|
oSearchEmployee.Name = oReader.GetString("name");
|
|||
|
|
oSearchEmployee.EmployeeNo = oReader.GetString("employeeNo");
|
|||
|
|
oSearchEmployee.LocationID = oReader.GetInt32("locationID", 0);
|
|||
|
|
oSearchEmployee.CategoryID = oReader.GetInt32("categoryID", 0);
|
|||
|
|
oSearchEmployee.DepartmentID = oReader.GetInt32("departmentID", true, 0);
|
|||
|
|
oSearchEmployee.designationID = oReader.GetInt32("designationID", true, 0);
|
|||
|
|
oSearchEmployee.designationName = oReader.GetString("DesignationName", true, string.Empty);
|
|||
|
|
oSearchEmployee.departmentName = oReader.GetString("DepartmentName", true, string.Empty);
|
|||
|
|
oSearchEmployee.gradeName = oReader.GetString("GradeName", true, string.Empty);
|
|||
|
|
oSearchEmployee.GradeID = oReader.GetInt32("GradeID", 0);
|
|||
|
|
oSearchEmployee.EmployeeEmail = oReader.GetString("Emailaddress", true, string.Empty);
|
|||
|
|
this.SetObjectState(oSearchEmployee, Ease.Core.ObjectState.Saved);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
protected override T CreateObject<T>(DataReader oReader)
|
|||
|
|
{
|
|||
|
|
SearchEmployee oSearchEmployee = new SearchEmployee();
|
|||
|
|
MapObject(oSearchEmployee, oReader);
|
|||
|
|
return oSearchEmployee as T;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
protected SearchEmployee CreateObject(DataReader oReader)
|
|||
|
|
{
|
|||
|
|
SearchEmployee oSearchEmployee = new SearchEmployee();
|
|||
|
|
MapObject(oSearchEmployee, oReader);
|
|||
|
|
return oSearchEmployee;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#region Service implementation
|
|||
|
|
public List<SearchEmployee> Search(string sql, bool withName)
|
|||
|
|
{
|
|||
|
|
List<SearchEmployee> searchEmployees = null;
|
|||
|
|
|
|||
|
|
TransactionContext tc = null;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
tc = TransactionContext.Begin();
|
|||
|
|
|
|||
|
|
DataReader dr = new DataReader(SearchEmployeeDA.Search(tc, sql, withName));
|
|||
|
|
searchEmployees = this.CreateObjects<SearchEmployee>(dr);
|
|||
|
|
dr.Close();
|
|||
|
|
|
|||
|
|
tc.End();
|
|||
|
|
}
|
|||
|
|
catch (Exception e)
|
|||
|
|
{
|
|||
|
|
#region Handle Exception
|
|||
|
|
|
|||
|
|
if (tc != null)
|
|||
|
|
tc.HandleError();
|
|||
|
|
ExceptionLog.Write(e);
|
|||
|
|
|
|||
|
|
throw new ServiceException(e.Message, e);
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return searchEmployees;
|
|||
|
|
}
|
|||
|
|
public List<SearchEmployee> Search(string sql, int userid, int payrolltypeid)
|
|||
|
|
{
|
|||
|
|
List<SearchEmployee> searchEmployees = null;
|
|||
|
|
|
|||
|
|
TransactionContext tc = null;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
tc = TransactionContext.Begin();
|
|||
|
|
|
|||
|
|
DataReader dr = new DataReader(SearchEmployeeDA.Search(tc, sql, userid, payrolltypeid));
|
|||
|
|
searchEmployees = this.CreateObjects<SearchEmployee>(dr);
|
|||
|
|
dr.Close();
|
|||
|
|
|
|||
|
|
tc.End();
|
|||
|
|
}
|
|||
|
|
catch (Exception e)
|
|||
|
|
{
|
|||
|
|
#region Handle Exception
|
|||
|
|
|
|||
|
|
if (tc != null)
|
|||
|
|
tc.HandleError();
|
|||
|
|
ExceptionLog.Write(e);
|
|||
|
|
|
|||
|
|
throw new ServiceException(e.Message, e);
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return searchEmployees;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
public SearchEmployee get(int empid)
|
|||
|
|
{
|
|||
|
|
SearchEmployee searchEmployees = null;
|
|||
|
|
|
|||
|
|
TransactionContext tc = null;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
tc = TransactionContext.Begin();
|
|||
|
|
|
|||
|
|
DataReader dr = new DataReader(SearchEmployeeDA.get(tc, empid));
|
|||
|
|
if (dr.Read())
|
|||
|
|
{
|
|||
|
|
searchEmployees = this.CreateObject<SearchEmployee>(dr);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
dr.Close();
|
|||
|
|
|
|||
|
|
//while (dr.Read())
|
|||
|
|
//{
|
|||
|
|
// SearchEmployee item = new SearchEmployee();
|
|||
|
|
// item.Name = dr.GetString("name");
|
|||
|
|
// item.EmployeeNo = dr.GetString("employeeNo");
|
|||
|
|
|
|||
|
|
// searchEmployees.Add(item);
|
|||
|
|
//}
|
|||
|
|
tc.End();
|
|||
|
|
}
|
|||
|
|
catch (Exception e)
|
|||
|
|
{
|
|||
|
|
#region Handle Exception
|
|||
|
|
|
|||
|
|
if (tc != null)
|
|||
|
|
tc.HandleError();
|
|||
|
|
ExceptionLog.Write(e);
|
|||
|
|
|
|||
|
|
throw new ServiceException(e.Message, e);
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return searchEmployees;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public SearchEmployee Get(int id, TransactionContext tc)
|
|||
|
|
{
|
|||
|
|
SearchEmployee searchEmployees = null;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
DataReader oreader = new DataReader(SearchEmployeeDA.get(tc, id));
|
|||
|
|
if (oreader.Read())
|
|||
|
|
{
|
|||
|
|
searchEmployees = this.CreateObject<SearchEmployee>(oreader);
|
|||
|
|
}
|
|||
|
|
oreader.Close();
|
|||
|
|
}
|
|||
|
|
catch (Exception e)
|
|||
|
|
{
|
|||
|
|
#region Handle Exception
|
|||
|
|
//if (tc != null)
|
|||
|
|
// tc.HandleError();
|
|||
|
|
//ExceptionLog.Write(e);
|
|||
|
|
throw new ServiceException(e.Message, e);
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return searchEmployees;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
public List<SearchEmployee> GetSelectedSearchEmployee(List<int> empids)
|
|||
|
|
{
|
|||
|
|
List<SearchEmployee> searchEmployees = new List<SearchEmployee>();
|
|||
|
|
string combinedString = string.Join(",", empids);
|
|||
|
|
|
|||
|
|
TransactionContext tc = null;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
tc = TransactionContext.Begin();
|
|||
|
|
//tc = TransactionContext.Begin();
|
|||
|
|
|
|||
|
|
DataReader dr = new DataReader(SearchEmployeeDA.GetByEmpIDIn(tc, combinedString));
|
|||
|
|
searchEmployees = this.CreateObjects<SearchEmployee>(dr);
|
|||
|
|
dr.Close();
|
|||
|
|
|
|||
|
|
tc.End();
|
|||
|
|
}
|
|||
|
|
catch (Exception e)
|
|||
|
|
{
|
|||
|
|
#region Handle Exception
|
|||
|
|
|
|||
|
|
if (tc != null)
|
|||
|
|
tc.HandleError();
|
|||
|
|
ExceptionLog.Write(e);
|
|||
|
|
|
|||
|
|
throw new ServiceException(e.Message, e);
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return searchEmployees;
|
|||
|
|
}
|
|||
|
|
public List<SearchEmployee> GetByEmpIDIn(string empids)
|
|||
|
|
{
|
|||
|
|
List<SearchEmployee> searchEmployees = new List<SearchEmployee>();
|
|||
|
|
|
|||
|
|
TransactionContext tc = null;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
tc = TransactionContext.Begin();
|
|||
|
|
tc = TransactionContext.Begin();
|
|||
|
|
|
|||
|
|
DataReader dr = new DataReader(SearchEmployeeDA.GetByEmpIDIn(tc, empids));
|
|||
|
|
searchEmployees = this.CreateObjects<SearchEmployee>(dr);
|
|||
|
|
dr.Close();
|
|||
|
|
|
|||
|
|
tc.End();
|
|||
|
|
}
|
|||
|
|
catch (Exception e)
|
|||
|
|
{
|
|||
|
|
#region Handle Exception
|
|||
|
|
|
|||
|
|
if (tc != null)
|
|||
|
|
tc.HandleError();
|
|||
|
|
ExceptionLog.Write(e);
|
|||
|
|
|
|||
|
|
throw new ServiceException(e.Message, e);
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return searchEmployees;
|
|||
|
|
}
|
|||
|
|
public List<SearchEmployee> FindEmpCodeName(int payrollTypeID, string code, string name)
|
|||
|
|
{
|
|||
|
|
List<SearchEmployee> searchEmployees = new List<SearchEmployee>();
|
|||
|
|
|
|||
|
|
TransactionContext tc = null;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
tc = TransactionContext.Begin();
|
|||
|
|
|
|||
|
|
DataReader dr = new DataReader(SearchEmployeeDA.Search(tc, payrollTypeID, code, name));
|
|||
|
|
searchEmployees = this.CreateObjects<SearchEmployee>(dr);
|
|||
|
|
//while (dr.Read())
|
|||
|
|
//{
|
|||
|
|
// SearchEmployee item = new SearchEmployee();
|
|||
|
|
// item.Name = dr.GetString("name");
|
|||
|
|
// item.EmployeeNo = dr.GetString("employeeNo");
|
|||
|
|
|
|||
|
|
// searchEmployees.Add(item);
|
|||
|
|
//}
|
|||
|
|
dr.Close();
|
|||
|
|
tc.End();
|
|||
|
|
}
|
|||
|
|
catch (Exception e)
|
|||
|
|
{
|
|||
|
|
#region Handle Exception
|
|||
|
|
|
|||
|
|
if (tc != null)
|
|||
|
|
tc.HandleError();
|
|||
|
|
ExceptionLog.Write(e);
|
|||
|
|
|
|||
|
|
throw new ServiceException(e.Message, e);
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return searchEmployees;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
public List<SearchEmployee> GetEmployeeNotYetUser(int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
List<SearchEmployee> searchEmployees = new List<SearchEmployee>();
|
|||
|
|
|
|||
|
|
TransactionContext tc = null;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
tc = TransactionContext.Begin();
|
|||
|
|
|
|||
|
|
DataReader dr = new DataReader(SearchEmployeeDA.GetEmployeeNotYetUser(tc, payrollTypeID));
|
|||
|
|
searchEmployees = this.CreateObjects<SearchEmployee>(dr);
|
|||
|
|
dr.Close();
|
|||
|
|
tc.End();
|
|||
|
|
}
|
|||
|
|
catch (Exception e)
|
|||
|
|
{
|
|||
|
|
#region Handle Exception
|
|||
|
|
|
|||
|
|
if (tc != null)
|
|||
|
|
tc.HandleError();
|
|||
|
|
ExceptionLog.Write(e);
|
|||
|
|
|
|||
|
|
throw new ServiceException(e.Message, e);
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return searchEmployees;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<SearchEmployee> GetTeam(int employeeid)
|
|||
|
|
{
|
|||
|
|
List<SearchEmployee> searchEmployees = new List<SearchEmployee>();
|
|||
|
|
|
|||
|
|
TransactionContext tc = null;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
tc = TransactionContext.Begin();
|
|||
|
|
|
|||
|
|
DataReader dr = new DataReader(SearchEmployeeDA.GetTeam(tc, employeeid));
|
|||
|
|
searchEmployees = this.CreateObjects<SearchEmployee>(dr);
|
|||
|
|
dr.Close();
|
|||
|
|
tc.End();
|
|||
|
|
}
|
|||
|
|
catch (Exception e)
|
|||
|
|
{
|
|||
|
|
#region Handle Exception
|
|||
|
|
|
|||
|
|
if (tc != null)
|
|||
|
|
tc.HandleError();
|
|||
|
|
ExceptionLog.Write(e);
|
|||
|
|
|
|||
|
|
throw new ServiceException(e.Message, e);
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return searchEmployees;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<SearchEmployee> GetTeam(int employeeid, EnumStatus enumStatus)
|
|||
|
|
{
|
|||
|
|
List<SearchEmployee> searchEmployees = new List<SearchEmployee>();
|
|||
|
|
|
|||
|
|
TransactionContext tc = null;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
tc = TransactionContext.Begin();
|
|||
|
|
|
|||
|
|
DataReader dr = new DataReader(SearchEmployeeDA.GetTeam(tc, employeeid, enumStatus));
|
|||
|
|
searchEmployees = this.CreateObjects<SearchEmployee>(dr);
|
|||
|
|
dr.Close();
|
|||
|
|
tc.End();
|
|||
|
|
}
|
|||
|
|
catch (Exception e)
|
|||
|
|
{
|
|||
|
|
#region Handle Exception
|
|||
|
|
|
|||
|
|
if (tc != null)
|
|||
|
|
tc.HandleError();
|
|||
|
|
ExceptionLog.Write(e);
|
|||
|
|
|
|||
|
|
throw new ServiceException(e.Message, e);
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return searchEmployees;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<SearchEmployee> GetTeamForAttnReguApprove(int employeeid, EnumStatus enumStatus)
|
|||
|
|
{
|
|||
|
|
List<SearchEmployee> searchEmployees = new List<SearchEmployee>();
|
|||
|
|
|
|||
|
|
TransactionContext tc = null;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
tc = TransactionContext.Begin();
|
|||
|
|
|
|||
|
|
DataReader dr = new DataReader(SearchEmployeeDA.GetTeamForAttenReguApprove(tc, employeeid, enumStatus));
|
|||
|
|
searchEmployees = this.CreateObjects<SearchEmployee>(dr);
|
|||
|
|
dr.Close();
|
|||
|
|
tc.End();
|
|||
|
|
}
|
|||
|
|
catch (Exception e)
|
|||
|
|
{
|
|||
|
|
#region Handle Exception
|
|||
|
|
|
|||
|
|
if (tc != null)
|
|||
|
|
tc.HandleError();
|
|||
|
|
ExceptionLog.Write(e);
|
|||
|
|
|
|||
|
|
throw new ServiceException(e.Message, e);
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return searchEmployees;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<SearchEmployee> FindCordinator(int? id, int? payrollTypeID)
|
|||
|
|
{
|
|||
|
|
List<SearchEmployee> searchEmployees = new List<SearchEmployee>();
|
|||
|
|
|
|||
|
|
TransactionContext tc = null;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
tc = TransactionContext.Begin();
|
|||
|
|
|
|||
|
|
DataReader dr = new DataReader(SearchEmployeeDA.FindCordinator(tc, id, payrollTypeID));
|
|||
|
|
searchEmployees = this.CreateObjects<SearchEmployee>(dr);
|
|||
|
|
dr.Close();
|
|||
|
|
tc.End();
|
|||
|
|
}
|
|||
|
|
catch (Exception e)
|
|||
|
|
{
|
|||
|
|
#region Handle Exception
|
|||
|
|
|
|||
|
|
if (tc != null)
|
|||
|
|
tc.HandleError();
|
|||
|
|
ExceptionLog.Write(e);
|
|||
|
|
|
|||
|
|
throw new ServiceException(e.Message, e);
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return searchEmployees;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<SearchEmployee> Find(SearchManager omanager)
|
|||
|
|
{
|
|||
|
|
string sql = "";
|
|||
|
|
this.ParametersValidation(omanager);
|
|||
|
|
this.FindRootTable(omanager);
|
|||
|
|
|
|||
|
|
sql = this.GetSubQuery(omanager);
|
|||
|
|
bool FromEmployee = (omanager.RootTable == "EMPLOYEE");
|
|||
|
|
|
|||
|
|
string withnametempqry = @", ' ' GradeName "
|
|||
|
|
+ ", ' ' DepartmentName "
|
|||
|
|
+ ", ' ' DesignationName ";
|
|||
|
|
string tempTableName = " INTO #tmpEmpSrc ";
|
|||
|
|
if (omanager.withName == false)
|
|||
|
|
{
|
|||
|
|
withnametempqry = "";
|
|||
|
|
tempTableName = "";
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (!FromEmployee)
|
|||
|
|
{
|
|||
|
|
sql = SQLParser.MakeSQL(
|
|||
|
|
"SELECT EmployeeID, LocationID, CategoryID, DepartmentID, GradeID, EmployeeNo, Name "
|
|||
|
|
+ " %q %q "
|
|||
|
|
+ " FROM Employee Where EmployeeID IN "
|
|||
|
|
+ " (Select %q.EmployeeID From %q) ORDER BY employeeno", withnametempqry, tempTableName, omanager.RootTable, sql);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
if (omanager.SearchFrom == EnumSearchFrom.Attendance)
|
|||
|
|
{
|
|||
|
|
tempTableName = " ";
|
|||
|
|
sql = SQLParser.MakeSQL(
|
|||
|
|
"SELECT distinct %q.EmployeeID, %q.LocationID, %q.CategoryID, %q.DesignationID, %q.DepartmentID, %q.GradeID, EmployeeNo, Name "
|
|||
|
|
+ " "
|
|||
|
|
+ " %q %q "
|
|||
|
|
+ "FROM Employee %q ORDER BY employeeno",
|
|||
|
|
omanager.RootTable, omanager.RootTable,
|
|||
|
|
omanager.RootTable, omanager.RootTable, omanager.RootTable, omanager.RootTable, withnametempqry, tempTableName, sql);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
//sql = SQLParser.MakeSQL(
|
|||
|
|
// "SELECT %q.EmployeeID, %q.LocationID, %q.CategoryID, %q.DesignationID, %q.DepartmentID, %q.GradeID, Employee.EmployeeNo, Employee.Name,Employee.Emailaddress "
|
|||
|
|
// + "FROM Employee %q ORDER BY employeeno",
|
|||
|
|
// omanager.RootTable, omanager.RootTable, omanager.RootTable, omanager.RootTable, omanager.RootTable,
|
|||
|
|
// omanager.RootTable, sql);
|
|||
|
|
sql = SQLParser.MakeSQL(
|
|||
|
|
"SELECT DISTINCT %q.EmployeeID, %q.LocationID, %q.CategoryID, %q.DesignationID, %q.DepartmentID, %q.GradeID, Employee.EmployeeNo, Employee.Name,Employee.Emailaddress "
|
|||
|
|
+ " %q %q FROM Employee %q ORDER BY employeeno",
|
|||
|
|
omanager.RootTable, omanager.RootTable, omanager.RootTable, omanager.RootTable, omanager.RootTable,
|
|||
|
|
omanager.RootTable, withnametempqry, tempTableName, sql);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
List<SearchEmployee> searchItems = new List<SearchEmployee>();
|
|||
|
|
if (omanager.checkDataPermission == false)
|
|||
|
|
searchItems = this.Search(sql, omanager.withName);
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
searchItems = this.Search(sql, (int)omanager.userid, (int)omanager.payrolltypeID);
|
|||
|
|
}
|
|||
|
|
return searchItems;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private string getCCSql(SearchManager omanager)
|
|||
|
|
{
|
|||
|
|
bool FromEmployee = (omanager.RootTable == "EMPLOYEE");
|
|||
|
|
string sql = "";
|
|||
|
|
SearchManager.SearchParameter op = omanager.Parameter.GetByParamer(EnumSearchParameter.CostCenterID);
|
|||
|
|
if (op == null) return "";
|
|||
|
|
if (!FromEmployee)
|
|||
|
|
{
|
|||
|
|
string dateTimeSyntax = omanager.Parameter.GetFromAndToDateSyntax();
|
|||
|
|
|
|||
|
|
sql = SQLParser.MakeSQL(
|
|||
|
|
" %q.EmployeeID IN (select distinct SALARYMONTHLY.EMPLOYEEID from SALARYEMPCOSTCENTER, SALARYMONTHLY where "
|
|||
|
|
+ " SALARYEMPCOSTCENTER.SALARYMONTHLYID =SALARYMONTHLY.SALARYMONTHLYID and SALARYMONTHLY.SalaryMonth =%q AND"
|
|||
|
|
+ " COSTCENTERID in(%q)) ", omanager.RootTable, dateTimeSyntax, op.ParameterValue.ToString());
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
sql = SQLParser.MakeSQL(
|
|||
|
|
" %q.EmployeeID IN (select distinct empCostcenter.EMPLOYEEID from empCostcenter where CurrentCC=1 AND COSTCENTERID in(%q))",
|
|||
|
|
omanager.RootTable, op.ParameterValue.ToString());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return sql;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
private void ParametersValidation(SearchManager omanager)
|
|||
|
|
{
|
|||
|
|
SearchManager.SearchParameter parameter = null;
|
|||
|
|
//if serach from is not Employee, From date must have in the paramer collection
|
|||
|
|
|
|||
|
|
if (omanager.SearchFrom != EnumSearchFrom.Employee)
|
|||
|
|
{
|
|||
|
|
parameter = omanager.Parameter.GetByParamer(EnumSearchParameter.FromDate);
|
|||
|
|
if (parameter == null) throw new ServiceException("From date/Month not found in the parameter");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//if To-date is in the parameter collection, From-date must be in the paramer collection
|
|||
|
|
|
|||
|
|
parameter = omanager.Parameter.GetByParamer(EnumSearchParameter.ToDate);
|
|||
|
|
if (parameter != null)
|
|||
|
|
{
|
|||
|
|
parameter = omanager.Parameter.GetByParamer(EnumSearchParameter.FromDate);
|
|||
|
|
if (parameter == null)
|
|||
|
|
throw new ServiceException(
|
|||
|
|
"To-Date/month is in the collection but From date/Month not found in the parameter");
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private void FindRootTable(SearchManager omanager)
|
|||
|
|
{
|
|||
|
|
omanager.RootTable = "EMPLOYEE";
|
|||
|
|
|
|||
|
|
switch (omanager.SearchFrom)
|
|||
|
|
{
|
|||
|
|
case EnumSearchFrom.Employee:
|
|||
|
|
omanager.RootTable = "EMPLOYEE";
|
|||
|
|
break;
|
|||
|
|
case EnumSearchFrom.Salary:
|
|||
|
|
omanager.RootTable = "SALARYMONTHLY";
|
|||
|
|
break;
|
|||
|
|
case EnumSearchFrom.Bonus:
|
|||
|
|
SearchManager.SearchParameter fromdateParamBonus =
|
|||
|
|
omanager.Parameter.GetByParamer(EnumSearchParameter.FromDate);
|
|||
|
|
if (fromdateParamBonus == null) return;
|
|||
|
|
if (Convert.ToDateTime(fromdateParamBonus.ParameterValue) <= omanager.LastPayProcessMonth)
|
|||
|
|
omanager.RootTable = "SALARYMONTHLY";
|
|||
|
|
break;
|
|||
|
|
case EnumSearchFrom.OutSidePayroll:
|
|||
|
|
SearchManager.SearchParameter fromdateParamOPI =
|
|||
|
|
omanager.Parameter.GetByParamer(EnumSearchParameter.FromDate);
|
|||
|
|
if (fromdateParamOPI == null) return;
|
|||
|
|
//if ((DateTime)fromdateParamOPI.ParameterValue <= SystemInformation.CurrentSysInfo.LastPayProcessDate)
|
|||
|
|
omanager.RootTable = "OPIProcessDetail";
|
|||
|
|
break;
|
|||
|
|
|
|||
|
|
case EnumSearchFrom.PFTran:
|
|||
|
|
|
|||
|
|
case EnumSearchFrom.OverTime:
|
|||
|
|
|
|||
|
|
case EnumSearchFrom.IT:
|
|||
|
|
SearchManager.SearchParameter taxParamid =
|
|||
|
|
omanager.Parameter.GetByParamer(EnumSearchParameter.TaxParameterID);
|
|||
|
|
if (taxParamid == null) break;
|
|||
|
|
;
|
|||
|
|
if (taxParamid.ParameterValue.ToString() == omanager.TaxParamID.ToString())
|
|||
|
|
{
|
|||
|
|
SearchManager.SearchParameter fromdateParamit =
|
|||
|
|
omanager.Parameter.GetByParamer(EnumSearchParameter.FromDate);
|
|||
|
|
if (fromdateParamit == null) return;
|
|||
|
|
if (Convert.ToDateTime(fromdateParamit.ParameterValue) <= omanager.LastPayProcessMonth)
|
|||
|
|
omanager.RootTable = "SALARYMONTHLY";
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
break;
|
|||
|
|
|
|||
|
|
case EnumSearchFrom.Loan:
|
|||
|
|
SearchManager.SearchParameter fromdateParam =
|
|||
|
|
omanager.Parameter.GetByParamer(EnumSearchParameter.FromDate);
|
|||
|
|
if (fromdateParam == null) return;
|
|||
|
|
if (Convert.ToDateTime(fromdateParam.ParameterValue) <= omanager.LastPayProcessMonth)
|
|||
|
|
omanager.RootTable = "SALARYMONTHLY";
|
|||
|
|
break;
|
|||
|
|
|
|||
|
|
default:
|
|||
|
|
omanager.RootTable = "EMPLOYEE";
|
|||
|
|
break;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//private bool FromEmployee
|
|||
|
|
//{
|
|||
|
|
// get
|
|||
|
|
// {
|
|||
|
|
// if (_rootTable == "EMPLOYEE") return true;
|
|||
|
|
// else return false;
|
|||
|
|
// }
|
|||
|
|
//}
|
|||
|
|
private string GetSubQuery(SearchManager omanager)
|
|||
|
|
{
|
|||
|
|
string subqsl = "";
|
|||
|
|
string dateTimeSyntax = "";
|
|||
|
|
dateTimeSyntax = omanager.Parameter.GetFromAndToDateSyntax();
|
|||
|
|
bool FromEmployee = (omanager.RootTable == "EMPLOYEE");
|
|||
|
|
switch (omanager.SearchFrom)
|
|||
|
|
{
|
|||
|
|
case EnumSearchFrom.Salary:
|
|||
|
|
subqsl = SQLParser.MakeSQL("SalaryMonthly Where Employee.EmployeeID = SalaryMonthly.EmployeeID "
|
|||
|
|
+ " AND SalaryMonth %q ", dateTimeSyntax);
|
|||
|
|
|
|||
|
|
SearchManager.SearchParameter tempSearchParam = omanager.Parameter.GetByParamer(EnumSearchParameter.BankID);
|
|||
|
|
if (tempSearchParam != null)
|
|||
|
|
{
|
|||
|
|
subqsl += SQLParser.MakeSQL(@" AND Employee.BRANCHID IN (SELECT BRANCHID FROM BRANCHES WHERE BANKID IN (%q)) ", tempSearchParam.ParameterValue);
|
|||
|
|
}
|
|||
|
|
break;
|
|||
|
|
case EnumSearchFrom.Bonus:
|
|||
|
|
string CompanyCode =
|
|||
|
|
ConfigurationManager.GetStringValue("system", "companycode", EnumConfigurationType.Logic);
|
|||
|
|
string[] s = dateTimeSyntax.Split('=');
|
|||
|
|
string[] s1 = s[1].Split('\'');
|
|||
|
|
DateTime d = GlobalFunctions.FirstDateOfMonth(Convert.ToDateTime(s1[1]));
|
|||
|
|
DateTime d1 = GlobalFunctions.LastDateOfMonth(Convert.ToDateTime(s1[1]));
|
|||
|
|
SearchManager.SearchParameter param = omanager.Parameter.GetByParamer(EnumSearchParameter.BonusId);
|
|||
|
|
if (param == null) throw new ServiceException("Bonus(Id) not found while searching from bonus");
|
|||
|
|
SearchManager.SearchParameter paramBatch = null;
|
|||
|
|
if (CompanyCode == "011")
|
|||
|
|
{
|
|||
|
|
paramBatch = omanager.Parameter.GetByParamer(EnumSearchParameter.BonusProcessID);
|
|||
|
|
if (paramBatch == null)
|
|||
|
|
throw new ServiceException("Batch No not found while searching from bonus");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (!FromEmployee)
|
|||
|
|
subqsl = SQLParser.MakeSQL(
|
|||
|
|
"SalaryMonthly,BONUSPROCESSDETAIL Where SalaryMonthly.EmployeeId=Employee.EmployeeID AND "
|
|||
|
|
+ " SalaryMonthly.EmployeeID = BONUSPROCESSDETAIL.EmployeeID"
|
|||
|
|
+ " AND BonusID=%n AND SalaryMonth=%d AND disburseDate between %d and %d",
|
|||
|
|
Convert.ToInt32(param.ParameterValue), d1, d, d1);
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
string Scode =
|
|||
|
|
ConfigurationManager.GetStringValue("system", "companycode", EnumConfigurationType.Logic);
|
|||
|
|
if (Scode == "011")
|
|||
|
|
{
|
|||
|
|
subqsl = SQLParser.MakeSQL(
|
|||
|
|
",BONUSPROCESSDETAIL Where Employee.EmployeeID = BONUSPROCESSDETAIL.EmployeeID "
|
|||
|
|
+ " AND BonusID=%n AND BonusProcessID=%n AND disburseDate between %d and %d ",
|
|||
|
|
Convert.ToInt32(param.ParameterValue), Convert.ToInt32(paramBatch.ParameterValue), d, d1);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
subqsl = SQLParser.MakeSQL(
|
|||
|
|
",BONUSPROCESSDETAIL Where Employee.EmployeeID = BONUSPROCESSDETAIL.EmployeeID "
|
|||
|
|
+ " AND BonusID=%n AND disburseDate between %d and %d ", Convert.ToInt32(param.ParameterValue), d,
|
|||
|
|
d1);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
SearchManager.SearchParameter tempSearchParamForBonus = omanager.Parameter.GetByParamer(EnumSearchParameter.BankID);
|
|||
|
|
if (tempSearchParamForBonus != null)
|
|||
|
|
{
|
|||
|
|
subqsl += SQLParser.MakeSQL(@" AND Employee.BRANCHID IN (SELECT BRANCHID FROM BRANCHES WHERE BANKID IN (%q)) ", tempSearchParamForBonus.ParameterValue);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
break;
|
|||
|
|
case EnumSearchFrom.OutSidePayroll:
|
|||
|
|
string opiID = "";
|
|||
|
|
string opiBranchID = "";
|
|||
|
|
SearchManager.SearchParameter paramOpi = omanager.Parameter.GetByParamer(EnumSearchParameter.OPIID);
|
|||
|
|
if (!FromEmployee)
|
|||
|
|
{
|
|||
|
|
if (paramOpi != null)
|
|||
|
|
{
|
|||
|
|
subqsl = SQLParser.MakeSQL(
|
|||
|
|
" OPIProcessDetail WHERE OPIProcessDetail.EmployeeID IN (SELECT DISTINCT opd.EmployeeID FROM OPIProcess op, OPIProcessDetail opd, OPIProcessDetailItem odi WHERE "
|
|||
|
|
+ " OPIMonth %q AND op.OPIProcessID = opd.OPIProcessID "
|
|||
|
|
+ " AND odi.OPIProcessDetailID = opd.OPIProcessDetailID AND odi.OPIItemID = %n)",
|
|||
|
|
dateTimeSyntax, (int)paramOpi.ParameterValue);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
//subqsl = SQLParser.MakeSQL(" SalaryMonthly WHERE SalaryMonthly.EmployeeID IN (SELECT DISTINCT opd.EmployeeID FROM OPIProcess op, OPIProcessDetail opd WHERE "
|
|||
|
|
// + " OPIMonth %q AND op.OPIProcessID = opd.OPIProcessID) ",
|
|||
|
|
// dateTimeSyntax);
|
|||
|
|
|
|||
|
|
subqsl = SQLParser.MakeSQL(
|
|||
|
|
" OPIProcessDetail WHERE OPIProcessDetail.EmployeeID IN (SELECT DISTINCT opd.EmployeeID FROM OPIProcess op, OPIProcessDetail opd WHERE "
|
|||
|
|
+ " OPIMonth %q AND op.OPIProcessID = opd.OPIProcessID) ",
|
|||
|
|
dateTimeSyntax);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
subqsl = SQLParser.MakeSQL(" ,OPIProcess op, OPIProcessDetail opd WHERE "
|
|||
|
|
+ " op.OPIMonth %q AND op.OPIProcessID = opd.OPIProcessID AND opd.EmployeeId=Employee.EmployeeID",
|
|||
|
|
dateTimeSyntax);
|
|||
|
|
|
|||
|
|
break;
|
|||
|
|
case EnumSearchFrom.PFTran:
|
|||
|
|
|
|||
|
|
//if (!this.FromEmployee)
|
|||
|
|
// subqsl = SQLParser.MakeSQL(" %q WHERE %q.EmployeeID IN (SELECT DISTINCT OPIPayment.EmployeeID FROM OPIPayment WHERE "
|
|||
|
|
// + " ForTheMonth=%d %q %q )", _rootTable, _rootTable, fromMonth, opiID, opiBranchID);
|
|||
|
|
//else
|
|||
|
|
// subqsl = SQLParser.MakeSQL(" Employee.EmployeeID IN (SELECT DISTINCT PF.EmployeeID FROM PFTransaction PF WHERE "
|
|||
|
|
// + " TranDate BETWEEN %d AND %d )", _rootTable, fromMonth, opiID, opiBranchID);
|
|||
|
|
//break;
|
|||
|
|
|
|||
|
|
case EnumSearchFrom.OverTime:
|
|||
|
|
string termID = "";
|
|||
|
|
if (!FromEmployee)
|
|||
|
|
subqsl = SQLParser.MakeSQL(
|
|||
|
|
" SalaryMonthly WHERE SalaryMonthly.EmployeeID IN (SELECT DISTINCT OTProcess.EmpID FROM OTProcess WHERE"
|
|||
|
|
+ " SalaryMonth=%q AND ProcessMonth=%q )", dateTimeSyntax, dateTimeSyntax);
|
|||
|
|
else
|
|||
|
|
subqsl = SQLParser.MakeSQL(
|
|||
|
|
" Where Employee.EmployeeID IN (SELECT DISTINCT OTProcess.EmpID FROM OTProcess WHERE "
|
|||
|
|
+ "ProcessMonth=%q)", dateTimeSyntax);
|
|||
|
|
|
|||
|
|
break;
|
|||
|
|
case EnumSearchFrom.Loan:
|
|||
|
|
if (!FromEmployee)
|
|||
|
|
subqsl = SQLParser.MakeSQL(
|
|||
|
|
" SalaryMonthly WHERE SalaryMonth =%q AND SalaryMonthly.EmployeeID IN (SELECT DISTINCT LOANISSUE.EmployeeID FROM LOANISSUE, LOANSCHEDULE WHERE "
|
|||
|
|
+ " LOANISSUE.LOANISSUEID= LOANSCHEDULE.LOANISSUEID AND DUEINSTALLMENTDATE=%q )",
|
|||
|
|
dateTimeSyntax, dateTimeSyntax);
|
|||
|
|
else
|
|||
|
|
subqsl = SQLParser.MakeSQL(
|
|||
|
|
" WHERE Employee.EmployeeID IN (SELECT DISTINCT LOANISSUE.EmployeeID FROM LOANISSUE, LOANSCHEDULE WHERE "
|
|||
|
|
+ " LOANISSUE.LOANISSUEID= LOANSCHEDULE.LOANISSUEID AND DUEINSTALLMENTDATE=%q)",
|
|||
|
|
dateTimeSyntax);
|
|||
|
|
break;
|
|||
|
|
case EnumSearchFrom.IT:
|
|||
|
|
// if selected fiscal year is current fiscal year, get data from incometaxtemp
|
|||
|
|
// if selected fiscal year is not current fs year, get data from incometaxyearly
|
|||
|
|
|
|||
|
|
|
|||
|
|
SearchManager.SearchParameter taxParamid =
|
|||
|
|
omanager.Parameter.GetByParamer(EnumSearchParameter.TaxParameterID);
|
|||
|
|
if (taxParamid == null) return "";
|
|||
|
|
//#####
|
|||
|
|
if (taxParamid.ParameterValue.ToString() == omanager.TaxParamID.ToString())
|
|||
|
|
{
|
|||
|
|
subqsl = SQLParser.MakeSQL(" SalaryMonthly WHERE SalaryMonth %q AND SalaryMonthly.EmployeeID IN (SELECT DISTINCT INCOMETAXTEMP.EmployeeID FROM INCOMETAXTEMP)",
|
|||
|
|
dateTimeSyntax);
|
|||
|
|
break;
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
//subqsl = SQLParser.MakeSQL(" SalaryMonthly WHERE SalaryMonth %q AND SalaryMonthly.EmployeeID IN (SELECT DISTINCT INCOMETAXYEARLY.EmployeeID FROM INCOMETAXYEARLY WHERE "
|
|||
|
|
// + " INCOMETAXYEARLY.TAXPARAMID In(%q))",
|
|||
|
|
// dateTimeSyntax, taxParamid.ParameterValue.ToString());
|
|||
|
|
|
|||
|
|
subqsl = SQLParser.MakeSQL(", SalaryMonthly WHERE SalaryMonth %q AND SalaryMonthly.EmployeeID IN (SELECT DISTINCT INCOMETAXYEARLY.EmployeeID FROM INCOMETAXYEARLY WHERE "
|
|||
|
|
+ " INCOMETAXYEARLY.TAXPARAMID In(%q)) AND %q.Employeeid = SalaryMonthly.EmployeeID",
|
|||
|
|
dateTimeSyntax, taxParamid.ParameterValue.ToString(), omanager.RootTable);
|
|||
|
|
break;
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
case EnumSearchFrom.Attendance:
|
|||
|
|
subqsl = SQLParser.MakeSQL(
|
|||
|
|
",DAILYATTNPROCESS WHERE Employee.EmployeeID = DAILYATTNPROCESS.EmployeeID "
|
|||
|
|
+ " AND ATTNDATE %q ", dateTimeSyntax);
|
|||
|
|
break;
|
|||
|
|
default:
|
|||
|
|
//{
|
|||
|
|
// SearchParameter workPlanGroupID = this.Parameter.GetByParamer(EnumSearchParameter.WorkPlanGroupID);
|
|||
|
|
// if (workPlanGroupID == null) return "";
|
|||
|
|
// else
|
|||
|
|
// {
|
|||
|
|
// subqsl = SQLParser.MakeSQL(",WorkPlanGroup, EmployeeWorkPlanSetup WHERE Employee.EmployeeID = EmployeeWorkPlanSetUp.EmployeeID AND EmployeeWorkPlanSetUp.WorkPlanGroupID=WorkPlanGroup.WorkPlanGroupID AND "
|
|||
|
|
// + " WorkPlanGroup.WorkPlanGroupID= %n ", workPlanGroupID.ParameterValue);
|
|||
|
|
// }
|
|||
|
|
// break;
|
|||
|
|
//}
|
|||
|
|
break;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//this.Parameter.AddDefaultParameter();
|
|||
|
|
subqsl = (subqsl == "")
|
|||
|
|
? omanager.Parameter.GetParameterSQL(omanager.RootTable, true)
|
|||
|
|
: subqsl + " " + omanager.Parameter.GetParameterSQL(omanager.RootTable, false);
|
|||
|
|
string sqlcc = getCCSql(omanager);
|
|||
|
|
if (sqlcc != "")
|
|||
|
|
subqsl = subqsl + ((subqsl == "") ? " WHERE " + getCCSql(omanager) : " AND " + getCCSql(omanager));
|
|||
|
|
if (omanager.SearchFrom == EnumSearchFrom.Salary && omanager.IsNegletWithheld)
|
|||
|
|
subqsl = subqsl + ((subqsl == "") ? " WHERE " + omanager.RootTable + ".salaryWithHeld=0" : " AND " + omanager.RootTable + ".salaryWithHeld=0");
|
|||
|
|
else if (omanager.SearchFrom == EnumSearchFrom.Salary && !omanager.IsNegletWithheld)
|
|||
|
|
subqsl = subqsl + ((subqsl == "") ? " WHERE " + omanager.RootTable + ".salaryWithHeld=1" : " AND " + omanager.RootTable + ".salaryWithHeld=1");
|
|||
|
|
return subqsl;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|