1009 lines
40 KiB
C#
1009 lines
40 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using Ease.CoreV35;
|
|
using Ease.CoreV35.Model;
|
|
using Ease.CoreV35.Caching;
|
|
using System.Data.Linq.Mapping;
|
|
using Ease.CoreV35.DataAccess;
|
|
namespace Payroll.BO
|
|
{
|
|
#region SearchEmployee
|
|
|
|
[Serializable]
|
|
public class SearchEmployee:AuditTrailBase
|
|
{
|
|
#region Cache Store
|
|
|
|
private static Cache _cache = new Cache(typeof(SearchEmployee));
|
|
|
|
#endregion
|
|
|
|
#region Constructor
|
|
|
|
public SearchEmployee()
|
|
{
|
|
_employeeID = null;
|
|
_name = string.Empty;
|
|
_employeeNo = string.Empty;
|
|
_locationID = null;
|
|
_categoryID = null;
|
|
_departmentID = null;
|
|
_gradeID = null;
|
|
_selected = false;
|
|
_employee = null;
|
|
_department = null;
|
|
_email = string.Empty;
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Properties
|
|
|
|
#region EmployeeID : ID
|
|
|
|
private ID _employeeID;
|
|
public ID EmployeeID
|
|
{
|
|
get { return _employeeID; }
|
|
set
|
|
{
|
|
base.OnPropertyChange<ID>("EmployeeID", _employeeID, value);
|
|
_employeeID = value;
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region name : string
|
|
|
|
private string _name;
|
|
public string Name
|
|
{
|
|
get { return _name; }
|
|
set
|
|
{
|
|
base.OnPropertyChange<string>("name", _name, value);
|
|
_name = value;
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Email : string
|
|
|
|
private string _email;
|
|
public string Email
|
|
{
|
|
get { return _email; }
|
|
set
|
|
{
|
|
base.OnPropertyChange<string>("Email", _email, value);
|
|
_email = value;
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region employeeNo : string
|
|
|
|
private string _employeeNo;
|
|
public string EmployeeNo
|
|
{
|
|
get { return _employeeNo; }
|
|
set
|
|
{
|
|
base.OnPropertyChange<string>("employeeNo", _employeeNo, value);
|
|
_employeeNo = value;
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region LocationID : ID
|
|
|
|
private ID _locationID;
|
|
public ID LocationID
|
|
{
|
|
get { return _locationID; }
|
|
set
|
|
{
|
|
base.OnPropertyChange<ID>("LocationID", _locationID, value);
|
|
_locationID = value;
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region CategoryID : ID
|
|
|
|
private ID _categoryID;
|
|
public ID CategoryID
|
|
{
|
|
get { return _categoryID; }
|
|
set
|
|
{
|
|
base.OnPropertyChange<ID>("CategoryID", _categoryID, value);
|
|
_categoryID = value;
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region DepartmentID : ID
|
|
|
|
private ID _departmentID;
|
|
public ID DepartmentID
|
|
{
|
|
get { return _departmentID; }
|
|
set
|
|
{
|
|
base.OnPropertyChange<ID>("DepartmentID", _departmentID, value);
|
|
_departmentID = value;
|
|
}
|
|
}
|
|
|
|
private Department _department;
|
|
public Department DepartmentObj
|
|
{
|
|
get
|
|
{
|
|
if (_department == null && _departmentID != null && !_departmentID.IsUnassigned && _departmentID.Integer > 0)
|
|
{
|
|
_department = Department.Get(_departmentID);
|
|
}
|
|
return _department;
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region GradeID : ID
|
|
|
|
private ID _gradeID;
|
|
public ID GradeID
|
|
{
|
|
get { return _gradeID; }
|
|
set
|
|
{
|
|
base.OnPropertyChange<ID>("GradeID", _gradeID, value);
|
|
_gradeID = value;
|
|
}
|
|
}
|
|
|
|
private Grade _grade;
|
|
public Grade GradeObj
|
|
{
|
|
get
|
|
{
|
|
if (_grade == null && _gradeID != null && !_gradeID.IsUnassigned && _gradeID.Integer > 0)
|
|
{
|
|
_grade = Grade.Get(_gradeID);
|
|
}
|
|
return _grade;
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region selected : bool
|
|
|
|
private bool _selected;
|
|
public bool Selected
|
|
{
|
|
get { return _selected; }
|
|
set
|
|
{
|
|
base.OnPropertyChange<bool>("selected", _selected, value);
|
|
_selected = value;
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region employee : Employee
|
|
|
|
private Employee _employee;
|
|
public Employee Employee
|
|
{
|
|
get
|
|
{
|
|
if (_employeeID.Integer > 0 && _employee == null)
|
|
{
|
|
_employee = new Employee();
|
|
_employee = Employee.Get(_employeeID);
|
|
}
|
|
return this._employee;
|
|
}
|
|
set
|
|
{
|
|
_employee = value;
|
|
}
|
|
}
|
|
#endregion
|
|
#region HREmployee : HREmployee
|
|
|
|
private HREmployee _hrEmployee;
|
|
public HREmployee HREmployee
|
|
{
|
|
get
|
|
{
|
|
if (_employeeID.Integer > 0 && _hrEmployee == null)
|
|
{
|
|
_hrEmployee = new HREmployee();
|
|
_hrEmployee = _hrEmployee.Get(_employeeID);
|
|
}
|
|
return this._hrEmployee;
|
|
}
|
|
set
|
|
{
|
|
_hrEmployee = value;
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
#region Service Factory ISearchEmployeeService : ISearchEmployeeService
|
|
|
|
internal static ISearchEmployeeService Service
|
|
{
|
|
get { return Services.Factory.CreateService<ISearchEmployeeService>(typeof(ISearchEmployeeService)); }
|
|
}
|
|
|
|
#endregion
|
|
|
|
#endregion
|
|
|
|
#region Functions
|
|
|
|
public static ObjectsTemplate<SearchEmployee> Search(string sql)
|
|
{
|
|
ObjectsTemplate<SearchEmployee> searchEmployees = null;
|
|
|
|
try
|
|
{
|
|
searchEmployees = Service.Search(sql);
|
|
}
|
|
catch (ServiceException e)
|
|
{
|
|
throw new Exception(e.Message, e);
|
|
}
|
|
|
|
#region Cache Footer
|
|
|
|
_cache.Add(searchEmployees, "Get");
|
|
|
|
#endregion
|
|
|
|
return searchEmployees;
|
|
}
|
|
|
|
public static SearchEmployee GetSearchEmployeeFromEmployee(Employee oEmp)
|
|
{
|
|
SearchEmployee oSearch = new SearchEmployee();
|
|
oSearch._employeeID = oEmp.ID;
|
|
oSearch._name = oEmp.Name;
|
|
oSearch._employeeNo = oEmp.EmployeeNo;
|
|
oSearch._locationID = oEmp.LocationID;
|
|
oSearch._categoryID = oEmp.CategoryID;
|
|
oSearch._departmentID = oEmp.DepartmentID;
|
|
oSearch._gradeID = oEmp.GradeID;
|
|
oSearch._selected = true;
|
|
oSearch._employee = oEmp;
|
|
oSearch._email = oEmp.EmailAddress;
|
|
return oSearch;
|
|
}
|
|
private ID ID
|
|
{
|
|
get
|
|
{
|
|
throw new ServiceException("Search employee object id is virtual, it can't be use");
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
public static bool IsEmployeeExist(ObjectsTemplate<SearchEmployee> semployees, ID employeId)
|
|
{
|
|
foreach (SearchEmployee item in semployees)
|
|
{
|
|
if (item.EmployeeID.Integer == employeId.Integer)
|
|
{
|
|
if (item.Selected == true)
|
|
return true;
|
|
else return false;
|
|
}
|
|
}
|
|
return false;
|
|
}
|
|
|
|
public string RefreshList()
|
|
{
|
|
throw new NotImplementedException();
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
#region ISearchEmployee Service
|
|
|
|
public interface ISearchEmployeeService
|
|
{
|
|
ObjectsTemplate<SearchEmployee> Search(string sql);
|
|
}
|
|
|
|
#endregion
|
|
|
|
public interface IEmployeeFind
|
|
{
|
|
ObjectsTemplate<SearchEmployee> Find();
|
|
}
|
|
public interface IEmployeeSearch : IEmployeeFind
|
|
{
|
|
EnumSearchFrom SearchFrom { set; }
|
|
}
|
|
public class SearchManager:IEmployeeSearch
|
|
{
|
|
private IEmployeeFind _iEmployeeSearch;
|
|
private EnumSearchFrom _searchFrom;
|
|
private EnumChronology _chronology;
|
|
private string _rootTable;
|
|
private string _processTable;
|
|
public SearchManager(IEmployeeFind isearch, EnumSearchFrom searchfrom)
|
|
{
|
|
_iEmployeeSearch = isearch;
|
|
_searchFrom = searchfrom;
|
|
_chronology = EnumChronology.NotNeeded;
|
|
|
|
}
|
|
public SearchManager(EnumSearchFrom searchFrom)
|
|
{
|
|
_searchFrom = searchFrom;
|
|
_chronology = EnumChronology.NotNeeded;
|
|
}
|
|
#region ISearchFrom Members
|
|
|
|
public EnumSearchFrom SearchFrom
|
|
{
|
|
set
|
|
{
|
|
_searchFrom = value;
|
|
}
|
|
}
|
|
|
|
public ObjectsTemplate<SearchEmployee> Find()
|
|
{
|
|
|
|
string sql = "";
|
|
_processTable = "";
|
|
this.ParametersValidation();
|
|
this.FindRootTable();
|
|
|
|
sql = this.GetSubQuery();
|
|
|
|
if(_processTable!="")
|
|
{
|
|
sql += " AND " + _processTable;
|
|
}
|
|
if (!this.FromEmployee)
|
|
{
|
|
sql = SQLParser.MakeSQL("SELECT EmployeeID, LocationID, CategoryID, DepartmentID, GradeID, EmployeeNo, Name,emailaddress "
|
|
+ " FROM Employee Where EmployeeID IN "
|
|
+ " (Select DISTINCT %q.EmployeeID From %q) ORDER BY EmployeeNo", _rootTable, sql);
|
|
}
|
|
else
|
|
{
|
|
sql = SQLParser.MakeSQL("SELECT %q.EmployeeID, %q.LocationID, %q.CategoryID, %q.DepartmentID, %q.GradeID, EmployeeNo, Name,emailaddress "
|
|
+ "FROM Employee %q ORDER BY EmployeeNo",
|
|
_rootTable, _rootTable, _rootTable, _rootTable, _rootTable, sql);
|
|
}
|
|
|
|
ObjectsTemplate<SearchEmployee> searchItems = SearchEmployee.Search(sql);
|
|
return searchItems;
|
|
}
|
|
private string getCCSql()
|
|
{
|
|
string sql = "";
|
|
SearchParameter op = this.Parameter.GetByParamer(EnumSearchParameter.CostCenterID);
|
|
if(op==null)return "";
|
|
if (!this.FromEmployee)
|
|
{
|
|
string dateTimeSyntax = this.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)) ", _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))",
|
|
_rootTable, op.ParameterValue.ToString());
|
|
}
|
|
return sql;
|
|
}
|
|
private SearchParameter _searchParameter;
|
|
public SearchParameter Parameter
|
|
{
|
|
get
|
|
{
|
|
if (_searchParameter == null)
|
|
{
|
|
_searchParameter = new SearchParameter();
|
|
_searchParameter.AddDefaultParameter();
|
|
}
|
|
return _searchParameter;
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
private void ParametersValidation()
|
|
{
|
|
SearchParameter parameter = null;
|
|
//if serach from is not Employee, From date must have in the paramer collection
|
|
if (_searchFrom != EnumSearchFrom.Employee)
|
|
{
|
|
parameter = this.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 = this.Parameter.GetByParamer(EnumSearchParameter.ToDate);
|
|
if (parameter != null)
|
|
{
|
|
parameter = this.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()
|
|
{
|
|
_rootTable = "EMPLOYEE";
|
|
|
|
switch (_searchFrom)
|
|
{
|
|
case EnumSearchFrom.Employee:
|
|
_rootTable = "EMPLOYEE";
|
|
break;
|
|
case EnumSearchFrom.Salary:
|
|
_rootTable = "SALARYMONTHLY";
|
|
break;
|
|
case EnumSearchFrom.Bonus:
|
|
SearchParameter fromdateParamBonus = this.Parameter.GetByParamer(EnumSearchParameter.FromDate);
|
|
if (fromdateParamBonus == null) return;
|
|
//if ((DateTime)fromdateParamBonus.ParameterValue <= SystemInformation.CurrentSysInfo.LastPayProcessDate)
|
|
// _rootTable = "SALARYMONTHLY";
|
|
_rootTable = "BONUSPROCESSDetail";
|
|
break;
|
|
case EnumSearchFrom.OutSidePayroll:
|
|
SearchParameter fromdateParamOPI = this.Parameter.GetByParamer(EnumSearchParameter.FromDate);
|
|
if (fromdateParamOPI == null) return;
|
|
//if ((DateTime)fromdateParamOPI.ParameterValue <= SystemInformation.CurrentSysInfo.LastPayProcessDate)
|
|
_rootTable = "EMPLOYEE";
|
|
break;
|
|
|
|
case EnumSearchFrom.PFTran:
|
|
|
|
case EnumSearchFrom.OverTime:
|
|
|
|
case EnumSearchFrom.IT:
|
|
SearchParameter taxParamid = this.Parameter.GetByParamer(EnumSearchParameter.TaxParameterID);
|
|
if (taxParamid == null) break; ;
|
|
if (taxParamid.ParameterValue.ToString() == Payroll.BO.SystemInformation.CurrentSysInfo.TaxParamID.ToString())
|
|
{
|
|
SearchParameter fromdateParamit = this.Parameter.GetByParamer(EnumSearchParameter.FromDate);
|
|
if (fromdateParamit == null) return;
|
|
if ((DateTime)fromdateParamit.ParameterValue <= SystemInformation.CurrentSysInfo.LastPayProcessDate)
|
|
_rootTable = "SALARYMONTHLY";
|
|
}
|
|
break;
|
|
|
|
case EnumSearchFrom.Loan:
|
|
SearchParameter fromdateParam = this.Parameter.GetByParamer(EnumSearchParameter.FromDate);
|
|
if (fromdateParam == null) return;
|
|
if ((DateTime)fromdateParam.ParameterValue <= SystemInformation.CurrentSysInfo.LastPayProcessDate)
|
|
_rootTable = "SALARYMONTHLY";
|
|
break;
|
|
default:
|
|
_rootTable = "EMPLOYEE";
|
|
break;
|
|
}
|
|
}
|
|
private bool FromEmployee
|
|
{
|
|
get
|
|
{
|
|
if (_rootTable == "EMPLOYEE") return true;
|
|
else return false;
|
|
}
|
|
}
|
|
private string GetSubQuery()
|
|
{
|
|
string subqsl = "";
|
|
string dateTimeSyntax = "";
|
|
dateTimeSyntax = this.Parameter.GetFromAndToDateSyntax();
|
|
switch (_searchFrom)
|
|
{
|
|
case EnumSearchFrom.Salary:
|
|
subqsl = SQLParser.MakeSQL("SalaryMonthly Where Employee.EmployeeID = SalaryMonthly.EmployeeID "
|
|
+ " AND SalaryMonth %q ", dateTimeSyntax);
|
|
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]));
|
|
SearchParameter param= this.Parameter.GetByParamer(EnumSearchParameter.BonusId);
|
|
if (param == null) throw new ServiceException("Bonus(Id) not found while searching from bonus");
|
|
SearchParameter paramBatch=null;
|
|
if(CompanyCode=="011")
|
|
{
|
|
paramBatch = this.Parameter.GetByParamer(EnumSearchParameter.BonusProcessID);
|
|
if (paramBatch == null) throw new ServiceException("Batch No not found while searching from bonus");
|
|
}
|
|
|
|
if (!this.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", (int)param.ParameterValue , d1, d, d1);
|
|
subqsl = SQLParser.MakeSQL("BONUSPROCESSDETAIL Where "
|
|
+ " Employee.EmployeeID = BONUSPROCESSDETAIL.EmployeeID"
|
|
+ " AND BonusID=%n AND disburseDate between %d and %d", (int)param.ParameterValue, 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 ", (int)param.ParameterValue, (int)paramBatch.ParameterValue, d, d1);
|
|
|
|
}
|
|
else
|
|
{
|
|
subqsl = SQLParser.MakeSQL(",BONUSPROCESSDETAIL Where Employee.EmployeeID = BONUSPROCESSDETAIL.EmployeeID "
|
|
+ " AND BonusID=%n AND disburseDate between %d and %d ", (int)param.ParameterValue , d, d1);
|
|
|
|
|
|
}
|
|
|
|
}
|
|
break;
|
|
case EnumSearchFrom.OutSidePayroll:
|
|
string opiID ="";
|
|
string opiBranchID ="";
|
|
SearchParameter paramOpi = this.Parameter.GetByParamer(EnumSearchParameter.OPIID);
|
|
if (!this.FromEmployee)
|
|
{
|
|
if (paramOpi != null)
|
|
{
|
|
subqsl = SQLParser.MakeSQL(" SalaryMonthly WHERE SalaryMonthly.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);
|
|
}
|
|
}
|
|
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.CarFuel:
|
|
string CarFuelID = "";
|
|
string CarFuelBranchID = "";
|
|
SearchParameter paramCarFuel = this.Parameter.GetByParamer(EnumSearchParameter.CarFuelID);
|
|
if (!this.FromEmployee)
|
|
{
|
|
if (paramCarFuel != null)
|
|
{
|
|
subqsl = SQLParser.MakeSQL(" SalaryMonthly WHERE SalaryMonthly.EmployeeID IN (SELECT DISTINCT opd.EmployeeID FROM CarFuelProcess op,CarFuelProcessDetail opd, CarFuelProcessDetailItem odi WHERE "
|
|
+ " CarFuelMonth %q AND op.CarFuelProcessID = opd.CarFuelProcessID "
|
|
+ " AND odi.CarFuelProcessDetailID = opd.CarFuelProcessDetailID AND odi.CarFuelItemID = %n)",
|
|
dateTimeSyntax, (int)paramCarFuel.ParameterValue);
|
|
}
|
|
else
|
|
{
|
|
subqsl = SQLParser.MakeSQL(" SalaryMonthly WHERE SalaryMonthly.EmployeeID IN (SELECT DISTINCT opd.EmployeeID FROM CarFuelProcess op, CarFuelProcessDetail opd WHERE "
|
|
+ " CarFuelMonth %q AND op.CarFuelProcessID = opd.CarFuelProcessID) ",
|
|
dateTimeSyntax);
|
|
}
|
|
}
|
|
else
|
|
subqsl = SQLParser.MakeSQL(" ,CarFuelProcess op, CarFuelProcessDetail opd WHERE "
|
|
+ " op.CarFuelMonth %q AND op.CarFuelProcessID = opd.CarFuelProcessID 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 (!this.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 (!this.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
|
|
SearchParameter taxParamid = this.Parameter.GetByParamer(EnumSearchParameter.TaxParameterID);
|
|
if(taxParamid==null) return "";
|
|
if (taxParamid.ParameterValue.ToString() == Payroll.BO.SystemInformation.CurrentSysInfo.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());
|
|
break;
|
|
|
|
}
|
|
//case EnumSearchFrom.Loan:
|
|
// 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);
|
|
|
|
default:
|
|
break;
|
|
}
|
|
//this.Parameter.AddDefaultParameter();
|
|
subqsl = (subqsl == "")? this.Parameter.GetParameterSQL(_rootTable, true) :
|
|
subqsl +" "+ this.Parameter.GetParameterSQL(_rootTable, false);
|
|
_processTable = this.Parameter._processTable;
|
|
string sqlcc = getCCSql();
|
|
if(sqlcc!="")
|
|
subqsl = subqsl + ((subqsl == "") ? " WHERE " + getCCSql() : " AND " + getCCSql());
|
|
|
|
return subqsl;
|
|
}
|
|
public ObjectsTemplate<SearchEmployee> FindTeam(int EpmID)
|
|
{
|
|
|
|
string sql = "";
|
|
this.ParametersValidation();
|
|
this.FindRootTable();
|
|
|
|
//sql = this.GetSubQuery();
|
|
if (!this.FromEmployee)
|
|
{
|
|
sql = SQLParser.MakeSQL("SELECT EmployeeID, LocationID, CategoryID, DepartmentID, GradeID, EmployeeNo, Name "
|
|
+ " FROM Employee Where EmployeeID IN(Select EmployeeID From Employee Where EmployeeID IN(Select EmployeeID from OrganEmployee Where NodeID IN("
|
|
+ " Select OrganogramID From Organogram Where ParentID=(Select NodeID from OrganEmployee Where EmployeeID=%n)))) ORDER BY EmployeeNo", _rootTable, EpmID);
|
|
}
|
|
else
|
|
{
|
|
sql = SQLParser.MakeSQL("SELECT %q.EmployeeID, %q.LocationID, %q.CategoryID, %q.DepartmentID, %q.GradeID, EmployeeNo, Name "
|
|
+ "FROM Employee Where EmployeeID IN(Select EmployeeID From Employee Where EmployeeID IN(Select EmployeeID from OrganEmployee Where NodeID IN("
|
|
+ " Select OrganogramID From Organogram Where ParentID=(Select NodeID from OrganEmployee Where EmployeeID=%n)))) ORDER BY EmployeeNo",
|
|
_rootTable, _rootTable, _rootTable, _rootTable, _rootTable, EpmID);
|
|
}
|
|
|
|
ObjectsTemplate<SearchEmployee> searchItems = SearchEmployee.Search(sql);
|
|
return searchItems;
|
|
}
|
|
public class SearchParameter
|
|
{
|
|
private List<SearchParameter> _searchParameters;
|
|
private EnumSearchParameter _parameter;
|
|
private EnumSearchObjDataType _dataType;
|
|
private object _parameterValue;
|
|
private string _paramSQL;
|
|
private EnumSQLOperator _sqlOperator;
|
|
public string _processTable = "";
|
|
private void Add(EnumSearchParameter paramter, EnumSearchObjDataType datatype, object value)
|
|
{
|
|
SearchParameter oparameter = new SearchParameter();
|
|
oparameter.Paramter = paramter;
|
|
oparameter.DataType = datatype;
|
|
oparameter.ParameterValue = value;
|
|
oparameter.SqlOperator = EnumSQLOperator.EqualTo;
|
|
_searchParameters.Add(oparameter);
|
|
}
|
|
public void Add(EnumSearchParameter paramter, EnumSQLOperator sqloperator, EnumSearchObjDataType datatype, object value)
|
|
{
|
|
SearchParameter oparameter = new SearchParameter();
|
|
oparameter.Paramter = paramter;
|
|
oparameter.DataType = datatype;
|
|
oparameter.ParameterValue = value;
|
|
oparameter.SqlOperator = sqloperator;
|
|
_searchParameters.Add(oparameter);
|
|
}
|
|
public void Add(EnumSearchParameter paramter, EnumSQLOperator sqloperator)
|
|
{
|
|
SearchParameter oparameter = new SearchParameter();
|
|
oparameter.Paramter = paramter;
|
|
oparameter.DataType = EnumSearchObjDataType.String;
|
|
oparameter.ParameterValue = "";
|
|
oparameter.SqlOperator = sqloperator;
|
|
_searchParameters.Add(oparameter);
|
|
}
|
|
public void AddDefaultParameter()
|
|
{
|
|
SearchParameter oparameter = new SearchParameter();
|
|
oparameter.Paramter = EnumSearchParameter.PayrollTypeID;
|
|
oparameter.DataType = EnumSearchObjDataType.Number;
|
|
oparameter.ParameterValue = User.CurrentUser.LogInPayrollTypeID;
|
|
oparameter.SqlOperator = EnumSQLOperator.EqualTo;
|
|
_searchParameters.Add(oparameter);
|
|
}
|
|
public void Add(SearchParameter oparameter)
|
|
{
|
|
if (oparameter != null)
|
|
{
|
|
_searchParameters.Add(oparameter);
|
|
}
|
|
}
|
|
public void Add(EnumSearchParameter paramter, bool value)
|
|
{
|
|
this.Add(paramter, EnumSearchObjDataType.boolean, value);
|
|
}
|
|
public void Remove(EnumSearchParameter paramter)
|
|
{
|
|
SearchParameter param = null;
|
|
foreach (SearchParameter item in this._searchParameters)
|
|
{
|
|
if (item.Paramter == EnumSearchParameter.PayrollTypeID)
|
|
{
|
|
param = item;
|
|
break;
|
|
}
|
|
}
|
|
if (param != null) _searchParameters.Remove(param);
|
|
}
|
|
public void Add(EnumSearchParameter paramter, int value)
|
|
{
|
|
this.Add(paramter, EnumSearchObjDataType.Number, value);
|
|
}
|
|
public void Add(EnumSearchParameter paramter, string value)
|
|
{
|
|
this.Add(paramter, EnumSearchObjDataType.String, value);
|
|
}
|
|
public void Add(EnumSearchParameter paramter, DateTime value)
|
|
{
|
|
this.Add(paramter, EnumSearchObjDataType.Date, value);
|
|
}
|
|
public SearchParameter()
|
|
{
|
|
_searchParameters = new List<SearchParameter>();
|
|
}
|
|
public EnumSearchParameter Paramter
|
|
{
|
|
get
|
|
{
|
|
return _parameter;
|
|
}
|
|
set
|
|
{
|
|
_parameter = value;
|
|
}
|
|
}
|
|
public EnumSearchObjDataType DataType
|
|
{
|
|
get
|
|
{
|
|
return _dataType;
|
|
}
|
|
set
|
|
{
|
|
_dataType = value;
|
|
}
|
|
}
|
|
public object ParameterValue
|
|
{
|
|
get
|
|
{
|
|
return _parameterValue;
|
|
}
|
|
set
|
|
{
|
|
_parameterValue = value;
|
|
}
|
|
}
|
|
public EnumSQLOperator SqlOperator
|
|
{
|
|
get
|
|
{
|
|
return _sqlOperator;
|
|
}
|
|
set
|
|
{
|
|
_sqlOperator = value;
|
|
}
|
|
}
|
|
|
|
public string GetParameterSQL(string rootTable, bool buildFromWhere)
|
|
{
|
|
SearchObject sObject = new SearchObject();
|
|
#region Create Search Object
|
|
int nCount = 1;
|
|
|
|
foreach (SearchParameter param in this._searchParameters)
|
|
{
|
|
|
|
string fieldName = GetFieldName(param.Paramter);
|
|
if (fieldName == "") continue;
|
|
if (fieldName == "ProcessCode")
|
|
{
|
|
_processTable = "";
|
|
_processTable = "SalaryProcessID="+param._parameterValue;
|
|
continue;
|
|
}
|
|
if (rootTable.ToLower() == "bonusprocessdetail" && fieldName.ToLower()=="payrolltypeid") continue;
|
|
fieldName = rootTable + "." + fieldName;
|
|
string value = "";
|
|
|
|
if (param.DataType == EnumSearchObjDataType.Date || param.DataType == EnumSearchObjDataType.DateWithTime)
|
|
{
|
|
value = ((DateTime)param.ParameterValue).ToString("dd MMM yyyy");
|
|
}
|
|
else value = param.ParameterValue.ToString();
|
|
|
|
if (fieldName == "EMPLOYEE.EmployeeNo")
|
|
{
|
|
param.SqlOperator = EnumSQLOperator.In;
|
|
}
|
|
if (fieldName == "EMPLOYEE.JoiningDate")
|
|
{
|
|
if (nCount == 1)
|
|
{
|
|
param.SqlOperator = EnumSQLOperator.GraterThan;
|
|
nCount++;
|
|
}
|
|
else
|
|
{
|
|
param.SqlOperator = EnumSQLOperator.SmallerThan;
|
|
}
|
|
|
|
}
|
|
|
|
sObject.Add(param.SqlOperator, fieldName, value, string.Empty, param.DataType);
|
|
|
|
}
|
|
|
|
return sObject.BuildQuery(buildFromWhere);
|
|
#endregion
|
|
|
|
}
|
|
public string GetFieldName(EnumSearchParameter parameter)
|
|
{
|
|
string fieldName = "";
|
|
switch (parameter)
|
|
{
|
|
case EnumSearchParameter.CategoryID:
|
|
fieldName = "CategoryID";
|
|
break;
|
|
case EnumSearchParameter.DesignationID:
|
|
fieldName = "DesignationID";
|
|
break;
|
|
case EnumSearchParameter.GradeID:
|
|
fieldName = "GradeID";
|
|
break;
|
|
case EnumSearchParameter.GradeSegmentID:
|
|
fieldName = "GradeID";
|
|
break;
|
|
case EnumSearchParameter.LocationID:
|
|
fieldName = "LOCATIONID";
|
|
break;
|
|
case EnumSearchParameter.ReligionID:
|
|
fieldName = "ReligionID";
|
|
break;
|
|
case EnumSearchParameter.DepartmentID:
|
|
fieldName = "DepartmentID";
|
|
break;
|
|
case EnumSearchParameter.BranchID:
|
|
fieldName = "BranchID";
|
|
break;
|
|
case EnumSearchParameter.Name:
|
|
fieldName = "Name";
|
|
break;
|
|
case EnumSearchParameter.Status:
|
|
fieldName = "Status";
|
|
break;
|
|
case EnumSearchParameter.Posted:
|
|
break;
|
|
case EnumSearchParameter.Confirm:
|
|
fieldName = "IsConfirmed";
|
|
break;
|
|
case EnumSearchParameter.OTEligible:
|
|
fieldName = "ISELIGIBLEOT";
|
|
break;
|
|
case EnumSearchParameter.BonusId:
|
|
break;
|
|
case EnumSearchParameter.Gender:
|
|
fieldName = "Gender";
|
|
break;
|
|
case EnumSearchParameter.PFMember:
|
|
fieldName = "PFMEMBERTYPE";
|
|
break;
|
|
case EnumSearchParameter.FinalizedSalary:
|
|
break;
|
|
case EnumSearchParameter.SalaryBarchID:
|
|
break;
|
|
case EnumSearchParameter.OPIID:
|
|
break;
|
|
case EnumSearchParameter.Expatriate:
|
|
fieldName = "FOREIGNEXPAT";
|
|
break;
|
|
case EnumSearchParameter.TermID:
|
|
break;
|
|
case EnumSearchParameter.AssingedAccCard:
|
|
fieldName = "CardID";
|
|
break;
|
|
case EnumSearchParameter.PayrollTypeID:
|
|
fieldName = "PayrollTypeID";
|
|
break;
|
|
case EnumSearchParameter.EmployeeNo:
|
|
fieldName = "EmployeeNo";
|
|
break;
|
|
case EnumSearchParameter.NationalID:
|
|
fieldName = "NationalID";
|
|
break;
|
|
case EnumSearchParameter.JoiningDate:
|
|
fieldName = "JoiningDate";
|
|
break;
|
|
case EnumSearchParameter.SalaryProcessCode:
|
|
fieldName = "ProcessCode";
|
|
break;
|
|
default:
|
|
break;
|
|
}
|
|
return fieldName;
|
|
}
|
|
public string GetFromAndToDateSyntax()
|
|
{
|
|
string syntax = "";
|
|
SearchParameter fromDate= _searchParameters.Find(delegate(SearchParameter item) { return (item.Paramter == EnumSearchParameter.FromDate); });
|
|
if (fromDate == null) return "";//throw new ServiceException("From Date/Month not found in the Search Parameter");
|
|
DateTime fromdate = (DateTime) fromDate.ParameterValue;
|
|
SearchParameter secondDate = _searchParameters.Find(delegate(SearchParameter item) { return (item.Paramter == EnumSearchParameter.ToDate); });
|
|
if (secondDate == null)
|
|
syntax = "=" + "'" + fromdate.ToString("dd MMM yyyy") + "'";
|
|
else
|
|
{
|
|
DateTime todate = (DateTime)secondDate.ParameterValue;
|
|
syntax = " BETWEEN '" + fromdate.ToString("dd MMM yyyy") + "' AND '"
|
|
+ todate.ToString("dd MMM yyyy") +"'";
|
|
}
|
|
return syntax;
|
|
}
|
|
|
|
public SearchParameter GetByParamer(EnumSearchParameter parameter)
|
|
{
|
|
SearchParameter sParameter = _searchParameters.Find(delegate(SearchParameter item) { return (item.Paramter == parameter); });
|
|
return sParameter;
|
|
}
|
|
}
|
|
|
|
}
|
|
|
|
|
|
|
|
}
|