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;
            }
        }

    }

    

}