/**
     * Return list of vacancy which have this status.
     */
    public static TransactionResponse getAllActiveVacancy(string status)
    {
        TransactionResponse response = new TransactionResponse();
        IDictionary<string, object> statusParams = new Dictionary<string, object>();
        statusParams.Add("@status", status);
        statusParams.Add("@districtId", PageAccessManager.getDistrictID());

        //Pass Stored Procedure Name and parameter list.
        DBOperationsUtil dpOperation = new DBOperationsUtil(DbAccessConstants.spAllActiveVacancy, statusParams);
        try
        {
            DataTable dataTable = dpOperation.getRecord();

            response.Data = dataTable;
            response.setSuccess(true);
        }
        catch (SqlException ex)
        {
            //Other SqlException is catched
            response.setSuccess(false);
            response.setMessageType(TransactionResponse.SeverityLevel.ERROR);
            response.setMessage(DBOperationErrorConstants.M_UNKNOWN_EVIL_ERROR);
            response.setErrorCode(DBOperationErrorConstants.E_UNKNOWN_ERROR_AT_DB_OOPERATION);
        }

        //CATCH ANY OTHER EXCEPTION, dont let user see any kind of unexpected error
        catch (Exception ex)
        {
            //Write this exception to file for investigation of the issue later.
            LoggerManager.LogError(ex.ToString(), logger);
            LoggerManager.upDateWithGenericErrorMessage(response);
        }
        return response;
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (Request.IsAuthenticated)
        {
            if (!IsPostBack)
            {
                populateVacancy();
            }

            // Get a reference to the currently logged on user 
            MembershipUser currentUser = Membership.GetUser();

            // Determine the currently logged on user's UserId value 
            Guid currentUserId = (Guid)currentUser.ProviderUserKey;

            //prepare parameters
            IDictionary<string, object> argumentsMap = new Dictionary<string, object>();
            argumentsMap.Add("@UserId", currentUserId);

            //Pass Stored Procedure Name and parameter list. 
            DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetEmployeeBranchDetail, argumentsMap);

            //call getRecord method and get DataTable
            DataTable dataTable = storeToDb.getRecord();
            if (dataTable != null)
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    loggedInEmpID = row["Emp_ID"].ToString();
                }
            }

        }
    }

    public static DataTable getAllBranchEmpStatus()
    {
        //Pass Stored Procedure Name and parameter list.
        DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetAllBranchEmployeeStatus, null);

        //call getRecord method and get DataTable
        DataTable dataTable = storeToDb.getRecord();

        return dataTable;
    }

    public static DataTable getAllBranchEmpStatus()
    {
        //Pass Stored Procedure Name and parameter list. 
        IDictionary<string, object> argumentsMap = new Dictionary<string, object>();
        argumentsMap.Add("@district", PageAccessManager.getDistrictID());

        DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetAllBranchEmployeeStatus, argumentsMap);

        //call getRecord method and get DataTable
        DataTable dataTable = storeToDb.getRecord();

        return dataTable;
    }

    public static DataTable getAssignedEmployeeFormOtherDistrict()
    {
        //prepare parameters
        IDictionary<string, object> argumentsMap = new Dictionary<string, object>();

        //Pass Stored Procedure Name and parameter list.
        DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetListOfAssignedEmployeefromOtherDistrict, argumentsMap);

        //call getRecord method and get DataTable
        DataTable dataTable = storeToDb.getRecord();

        return dataTable;
    }

    public static DataTable getSpecBranchEmpStatus(int branchID)
    {
        //prepare parameters
        IDictionary<string, object> argumentsMap = new Dictionary<string, object>();
        argumentsMap.Add("@branch", branchID);

        //Pass Stored Procedure Name and parameter list. 
        DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetBranchEmployeeStatus, argumentsMap);

        //call getRecord method and get DataTable
        DataTable dataTable = storeToDb.getRecord();

        return dataTable;
    }

    public static TransactionResponse getAllNotificationsForCurrentEmployee(MembershipUser currentUser)
    {
        //get detail of the logged on user.
        Employee employee = EmployeeManager.getLoggedOnUser((Guid)currentUser.ProviderUserKey);

        if (employee == null)
        {
            return EmployeeManager.handleLoggedInUserCanNotBeIdentfied();
        }

        TransactionResponse response = new TransactionResponse();
        try
        {
            IDictionary<string, object> employeeIdMap = new Dictionary<string, object>();
            employeeIdMap.Add("@EMP_ID", employee.EmpID);
            employeeIdMap.Add("@destrictID", PageAccessManager.getDistrictID());

            //Pass Stored Procedure Name and parameter list.
            DBOperationsUtil dbOperation = new DBOperationsUtil(DbAccessConstants.spGetAllNotificationForTheCurrentEmployee, employeeIdMap);
            DataTable dataTable = dbOperation.getRecord();
            //put the data on Transaction response
            response.Data = dataTable;
            response.setSuccess(true);
            response.setMessageType(TransactionResponse.SeverityLevel.INFO);
            response.setMessage(DBOperationErrorConstants.M_NOTIFICATION_INFO);
            //get Notifications inside the TransactionResponse.
            return response;
        }
        catch (SqlException ex)
        {
            response.setErrorCode(DBOperationErrorConstants.E_ERROR_WHILE_READING_NOTIFICATION);
            response.setMessage(DBOperationErrorConstants.M_ERROR_WHILE_READING_NOTIF);
            response.setMessageType(TransactionResponse.SeverityLevel.ERROR);
            response.setSuccess(false);
            return response;
        }

           //CATCH ANY OTHER EXCEPTION, dont let user see any kind of unexpected error
        catch (Exception ex)
        {
            //Write this exception to file for investigation of the issue later.
            LoggerManager.LogError(ex.ToString(), logger);
            response.setErrorCode(DBOperationErrorConstants.E_UNKNOWN_EVIL_ERROR);
            response.setMessage(DBOperationErrorConstants.M_UNKNOWN_EVIL_ERROR);
            response.setMessageType(TransactionResponse.SeverityLevel.ERROR);
            response.setSuccess(false);
            return response;
        }
    }

    public static TransactionResponse getDistrictSettingValue(string paramTag)
    {
        TransactionResponse response = new TransactionResponse();

        IDictionary<string, object> argumentsMap = new Dictionary<string, object>();
        argumentsMap.Add("@paramter_tag", paramTag);
        argumentsMap.Add("@districtID", PageAccessManager.getDistrictID());

        //Pass Stored Procedure Name and parameter list.
        DBOperationsUtil dbOperation = new DBOperationsUtil(DbAccessConstants.spGetDistrictSetting, argumentsMap);
        try
        {
            DataTable dataTable = dbOperation.getRecord();
            if (dataTable != null)
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    //put the data on Transaction reponse
                    response.Data = row["parameter_value"].ToString();
                    response.setSuccess(true);
                    return response;
                }
            }
            response.Data = PARAMETER_NOT_DEFINED + DBOperationErrorConstants.CONTACT_ADMIN;
            response.setSuccess(false);
            return response;
        }
        catch (SqlException ex)
        {
            response.Data = PARAMETER_NOT_DEFINED + DBOperationErrorConstants.CONTACT_ADMIN;
            response.setSuccess(false);
            return response;
        }

        //CATCH ANY OTHER EXCEPTION, dont let user see any kind of unexpected error
        catch (Exception ex)
        {
            //Write this exception to file for investigation of the issue later.
            LoggerManager.LogError(ex.ToString(), logger);
            response.Data = PARAMETER_NOT_DEFINED + DBOperationErrorConstants.CONTACT_ADMIN;
            response.setSuccess(false);
            return response;
        }
    }

    private TransactionResponse sendInsertPromotionToDb(string spName, IDictionary<string, object> parameters)
    {
        //Pass Stored Procedure Name and parameter list. 
        DBOperationsUtil storeToDb = new DBOperationsUtil(spName, parameters);

        TransactionResponse response = new TransactionResponse();
        try
        {
            //call store to DB mathod and get reponse. 
            storeToDb.instertNewRecord();
            response.setSuccess(true);
            response.setMessageType(TransactionResponse.SeverityLevel.SUCESS);
            response.setMessage(DBOperationErrorConstants.M_PROMOTION_REGISTER_OK);
        }

        catch (SqlException ex) 
        {
            //Determine if the cause was duplicate KEY.
            if (ex.ToString().Contains(DBOperationErrorConstants.PK_DUPLICATE_INDICATOR))
            {
                response.setMessageType(TransactionResponse.SeverityLevel.ERROR);
                response.setMessage(DBOperationErrorConstants.M_DUPLICATE_PROMOTION_KEY_ERROR);
                response.setErrorCode(DBOperationErrorConstants.E_DUPLICATE_KEY_ERROR);
            }
            else
            {
                //Other SqlException is catched
                response.setMessageType(TransactionResponse.SeverityLevel.ERROR);
                response.setMessage(DBOperationErrorConstants.M_UNKNOWN_ERROR_APP_RATING_REGISTER);
                response.setErrorCode(DBOperationErrorConstants.E_UNKNOWN_ERROR_AT_DB_OOPERATION);
            }
        }

        //CATCH ANY OTHER EXCEPTION, dont let user see any kind of unexpected error
        catch (Exception ex)
        {            
            //Write this exception to file for investigation of the issue later. 
            logException(ex);
            LoggerManager.upDateWithGenericErrorMessage(response);
        }

        return response;
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (Request.IsAuthenticated)
        {
            // Get a reference to the currently logged on user 
            MembershipUser currentUser = Membership.GetUser();

            // Determine the currently logged on user's UserId value 
            Guid currentUserId = (Guid)currentUser.ProviderUserKey;

            //prepare parameters
            IDictionary<string, object> argumentsMap = new Dictionary<string, object>();
            argumentsMap.Add("@UserId", currentUserId);

            //Pass Stored Procedure Name and parameter list. 
            DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetEmployeeBranchDetail, argumentsMap);

            //call getRecord method and get DataTable
            DataTable dataTable = storeToDb.getRecord();
            if (dataTable != null)
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    fName = row["First_Name"].ToString();
                    mName = row["Middle_Name"].ToString();
                    jTittle = row["Job_Tittle"].ToString();
                    branchName = row["branchName"].ToString();
                    branchID = Convert.ToInt16(row["branch"].ToString());
                }
            }
            else
            {
                //something went wrong, show correct error to the user
            }
            lblBranch.Text = branchName;
        }
        if (!IsPostBack)
        {
            DataTable getds = BranchEmployeeStatusManager.getSpecBranchEmpStatus(branchID);
            BindDataSetToGV(getds);
        }
    }

    public TransactionResponse addNewPromotionAssignment()
    {
        //Add List of Arguments for new promotion Assigment
        IDictionary<string, object> promotionAssigmentParameters = new Dictionary<string, object>();

        promotionAssigmentParameters.Add("@minNo", promotionAssigment.MinuteNo);
        promotionAssigmentParameters.Add("@hROfficerID", promotionAssigment.HROfficerID);
        promotionAssigmentParameters.Add("@deadLine", promotionAssigment.DeadLine);
        promotionAssigmentParameters.Add("@remark", promotionAssigment.Remark);

        //Pass Stored Procedure Name and parameter list.
        DBOperationsUtil dpOperation = new DBOperationsUtil(DbAccessConstants.spAddPromotionAssignmentToHROfficer, promotionAssigmentParameters);
        TransactionResponse response = new TransactionResponse();
        try
        {
            //call update to DB mathod and get reponse.
            dpOperation.updateRecord();
            response.setSuccess(true);
            response.setMessageType(TransactionResponse.SeverityLevel.SUCESS);
            response.setMessage(DBOperationErrorConstants.M_VACANCY_ASSIGNED_SUCCESS);
        }

        catch (SqlException ex)
        {
            //Other SqlException is catched
            response.setMessageType(TransactionResponse.SeverityLevel.ERROR);
            response.setMessage(DBOperationErrorConstants.PK_DUPLICATE_INDICATOR + ". "+ DBOperationErrorConstants.M_DUPLICATE_PROMOTION_ASSIGNEMENT);
            response.setErrorCode(DBOperationErrorConstants.E_PROMOTION_ASSIGNEMETN_FAILED);
        }

        //CATCH ANY OTHER EXCEPTION, dont let user see any kind of unexpected error
        catch (Exception ex)
        {
            //Write this exception to file for investigation of the issue later.
            logException(ex);
            LoggerManager.upDateWithGenericErrorMessage(response);
        }
        return response;
    }

    /**
     * gets list of Current branch
     */
    public static DataTable getCurrentBranch()
    {
        //Pass Stored Procedure Name and parameter list.
        DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetBranchsList, null);

        DataTable dataTable = null;
        try
        {
            return dataTable = storeToDb.getRecord();
        }
        catch (SqlException ex)
        {
            //return emptylist
            return dataTable;
        }

        //CATCH ANY OTHER EXCEPTION, dont let user see any kind of unexpected error
        catch (Exception ex)
        {
            //Write this exception to file for investigation of the issue later.
            LoggerManager.LogError(ex.ToString(), logger);
            return dataTable;
        }
    }

    public DataTable getUnassignedPromotion()
    {
        //Pass Stored Procedure Name and parameter list.
        IDictionary<string, object> parameters = new Dictionary<string, object>();
        parameters.Add("@districtID", PageAccessManager.getDistrictID());
        DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetPromotionAssignedToHROfficer, parameters);

        DataTable dataTable = null;
        try
        {
            return dataTable = storeToDb.getRecord();
        }
        catch (SqlException ex)
        {
            //return emptylist
            return dataTable;
        }

        //CATCH ANY OTHER EXCEPTION, dont let user see any kind of unexpected error
        catch (Exception ex)
        {
            //Write this exception to file for investigation of the issue later.
            logException(ex);
            return dataTable;
        }
    }

    //this methode return a list of promoted or Assigned Employee for specified Post
    public DataTable getSpecifiedPromotionResult(int postID, string promotionOrAssigned)
    {
        //prepare parameters
        IDictionary<string, object> argumentsMap = new Dictionary<string, object>();
        argumentsMap.Add("@Post", postID);
        argumentsMap.Add("@status", promotionOrAssigned);

        //Pass Stored Procedure Name and parameter list.
        DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetPromotedEmpBasedOnPostAndStatus, argumentsMap);

        //call getRecord method and get DataTable
        DataTable dataTable = storeToDb.getRecord();

        return dataTable;
    }

    public DataTable getmanagerialPosition(string branch)
    {
        //Pass Stored Procedure Name and parameter list.
        //Add List of Arguments TO GET managerial position
        IDictionary<string, object> branchID = new Dictionary<string, object>();

        branchID.Add("@branch", branch);
        DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetEmpAtManagerialPosition, branchID);

        DataTable dataTable = null;
        try
        {
            return dataTable = storeToDb.getRecord();
        }
        catch (SqlException ex)
        {
            //return emptylist
            return dataTable;
        }

        //CATCH ANY OTHER EXCEPTION, dont let user see any kind of unexpected error
        catch (Exception ex)
        {
            //Write this exception to file for investigation of the issue later.
            logException(ex);
            return dataTable;
        }
    }

    public DataTable getAllPromotedEmployee(string promotionStatus)
    {
        //prepare parameters
        IDictionary<string, object> argumentsMap = new Dictionary<string, object>();
        argumentsMap.Add("@Promostatus", promotionStatus);

        DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetAllPromotedEmp, argumentsMap);

        //call getRecord method and get DataTable
        DataTable dataTable = storeToDb.getRecord();

        return dataTable;
    }

    public DataTable UpdateemployeeEducationalQual(string EID, string eduLevel, string qualif)
    {
        //prepare parameters
        IDictionary<string, object> argumentsMap = new Dictionary<string, object>();
        argumentsMap.Add("@Emp_ID", EID);
        argumentsMap.Add("@EducationLevel", eduLevel);
        argumentsMap.Add("@Qualification", qualif);

        //Pass Stored Procedure Name and parameter list.
        DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spUpdateEmpEducLevel, argumentsMap);

        //call getRecord method and get DataTable
        DataTable dataTable = storeToDb.getRecord();

        return dataTable;
    }

    //delete resigned employee from database
    public TransactionResponse deleteResignedEmployee()
    {
        TransactionResponse response = new TransactionResponse();
        try
        {
            IDictionary<string, object> argumentMap = new Dictionary<string, object>();
            //task referance
            argumentMap.Add("@UserId", employee.UserName);

            //Pass Stored Procedure Name and parameter list.
            DBOperationsUtil dbOperation = new DBOperationsUtil(DbAccessConstants.spDeleteEmployeeInfor, argumentMap);
            bool isDeleteOk = dbOperation.deleteRecord();

            //put the data on Transaction response
            response.Data = isDeleteOk;

            response.setSuccess(true);

            //get delete status inside the TransactionResponse.
            return response;
        }
        catch (SqlException ex)
        {
            response.setErrorCode(DBOperationErrorConstants.E_ERROR_WHILE_REMOVING_INACTIVE_EMPLOYEE);
            response.setMessage(DBOperationErrorConstants.M_UNABLE_TO_REMOVING_INACTIVE_EMPLOYEE);
            response.setMessageType(TransactionResponse.SeverityLevel.ERROR);
            response.setSuccess(false);
            return response;
        }

           //CATCH ANY OTHER EXCEPTION, dont let user see any kind of unexpected error
        catch (Exception ex)
        {
            //Write this exception to file for investigation of the issue later.
            logException(ex);
            response.setErrorCode(DBOperationErrorConstants.E_UNKNOWN_EVIL_ERROR);
            response.setMessage(DBOperationErrorConstants.M_UNKNOWN_EVIL_ERROR);
            response.setMessageType(TransactionResponse.SeverityLevel.ERROR);
            response.setSuccess(false);
            return response;
        }
    }

    private DBOperationsUtil getDBOperationToUpdatePromotionStatus4Assignment(IDictionary<string, object> parameters, Promotion promotion, string promoStatus)
    {
        DBOperationsUtil dpOperation = null;

        //Pass Stored Procedure Name and parameter list.
        parameters.Add("@Emp_ID", 0);
        parameters.Add("@Minute_No", promotion.MinuteNo);
        parameters.Add("@vacancy_No", 0);
        parameters.Add("@forAssOrAnnounce", promoStatus);

        dpOperation = new DBOperationsUtil(DbAccessConstants.spUpdatePromotionStatus, parameters);
        return dpOperation;
    }

    /**
     * gets list of employee for a given Branch
     */
    public List<Employee> getListOfEmployeeByFirstNameOrEmpIdAtBranch()
    {
        IDictionary<string, object> argumentsMap = new Dictionary<string, object>();
        argumentsMap.Add("@txtEmpID", employee.UserName);
        argumentsMap.Add("@branch", employee.Branch);

        //Pass Stored Procedure Name and parameter list.
        DBOperationsUtil storeToDb = new DBOperationsUtil(DbAccessConstants.spGetEmployeeByFirstNameAutoCompleteSpecBranch, argumentsMap);

        //call getRecord method and get SqlDataReader
        DataTable dataTable = null;
        try
        {
            dataTable = storeToDb.getRecord();

            //format the Result to return to presentation layer.
            List<Employee> listOfEmployee = null;

            if (dataTable != null)
            {
                listOfEmployee = new List<Employee>();
                foreach (DataRow row in dataTable.Rows)
                {
                    Employee emply = new Employee();
                    emply.FName = row["First_Name"].ToString();
                    emply.MName = row["Middle_Name"].ToString();
                    emply.LName = row["Last_Name"].ToString();
                    emply.EmpID = row["Emp_ID"].ToString();
                    listOfEmployee.Add(emply);
                }
            }
            return listOfEmployee;
        }
        catch (SqlException ex)
        {
            //return emptylist
            return new List<Employee>();
        }

        //CATCH ANY OTHER EXCEPTION, dont let user see any kind of unexpected error
        catch (Exception ex)
        {
            //Write this exception to file for investigation of the issue later.
            logException(ex);
            return new List<Employee>();
        }
    }