Beispiel #1
0
        /// <summary>
        /// This function fetches the SCT Details from the database based on PReqNo
        /// </summary>
        /// <param name="PReqNo"></param>
        /// <returns>
        /// <paramref name="SCT_Entity"/>
        /// </returns>
        /// <history>
        ///     Hari haran      17/07/2012      created
        /// </history>
        public SCT_Emp searchEmployee_DAL(string Name_DAL, string MgrId_DAL ,string Flag)
        {
            logger.Info("Method : searchEmployee_DAL Start");
            logger.DebugFormat("Input parameter Name : {0} ", Name_DAL);
            logger.DebugFormat("Input parameter ManagerId : {0} ", MgrId_DAL);

            databaseLayer dbConStr = new databaseLayer();
            string connStr = dbConStr.connectionInfo;

            logger.Debug("Connection string : " + connStr);

            SqlConnection conn = new SqlConnection(connStr);

            if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();
                logger.Debug("Connection Status opened ");
            }

            logger.Info("Connetion to the database established");

            try
            {
                SqlCommand cmd_SCTDetails = new SqlCommand();
                cmd_SCTDetails.Connection = conn;
                string Query = string.Empty;

                switch (Flag)
                {
                    case "0":
                        {

                            SqlCommand cmd_empFunc = new SqlCommand();
                            cmd_empFunc.Connection = conn;
                            cmd_empFunc.CommandText = "SELECT [Function] FROM employees WHERE EmployeeNumber = @MgrID ";
                            cmd_empFunc.Parameters.AddWithValue("@MgrID",Convert.ToInt32(MgrId_DAL));

                            QueryLog.CmdInfo(cmd_empFunc);

                            string func = cmd_empFunc.ExecuteScalar().ToString();

                            logger.DebugFormat("Manager ID {0} - Funtion type {1}", MgrId_DAL, func);

                            if (func.Equals("ENGG"))
                            {
                                logger.Info("Search Type : Manager Search - Function : ENGG");

                                Query = "select e.EMPLOYEENUMBER as EmployeeNumber,e.EMPLOYEENAME as employeeName,c.codedescription Grade from Employees e,codes c ";
                                Query += "where codedescription in ('E5','E6','E7','E8','VP','AVP','President & COO','SVP','A5','A6','A7','N5','N6','N7','C5','C6','C7') ";
                                Query += "and codetype='Grade' and code=e.currentgrade and EmployeeName like @Name and EmployeeStatus='E'";
                            }
                            else
                            {
                                logger.Info("Search Type : Manager Search - Function : NON ENGG");

                                Query = "select e.EMPLOYEENUMBER as EmployeeNumber ,e.EMPLOYEENAME as employeeName,c.codedescription Grade from Employees e,codes c ";
                                Query += "where codedescription in ('E4','E5','E6','E7','E8','VP','AVP','President & COO','SVP','A4','A5','A6','A7','N4','N5','N6','N7','C5','C6','C7') ";
                                Query += "and codetype='Grade' and code=e.currentgrade and EmployeeName like @Name and EmployeeStatus='E'";
                            }

                            cmd_SCTDetails.Parameters.AddWithValue("@Name", "%" + Name_DAL + "%");

                        }
                        break;

                    case "1":
                    default:
                        {
                            logger.Info("Search Type : Employee Search");

                            Query = "select EmployeeNumber,employeeName,c.Codedescription Grade from Employees,Codes C,HRMSAC_EMP_COSTCENTER_SUBAREA ";
                            Query += "where EmployeeNumber=ECS_EMPNO and EmployeeStatus='E' and EmployeeNumber>1000 ";
                            Query += "and CurrentGrade=c.code and codetype='grade' and ";
                            Query += "reportingto=(select top 1 emailid from employees where employeenumber= @MgrId) and EmployeeName like @Name ";
                            cmd_SCTDetails.Parameters.AddWithValue("@Name", "%" + Name_DAL + "%");
                            cmd_SCTDetails.Parameters.Add("@MgrId", SqlDbType.Int);
                            cmd_SCTDetails.Parameters["@MgrId"].Value = Convert.ToInt32(MgrId_DAL);

                        }
                        break;

                }

                cmd_SCTDetails.CommandText = Query;

                logger.Info("cmd_SCTDetails Query parameters initialised");

                QueryLog.CmdInfo(cmd_SCTDetails);

                SqlDataAdapter da_SCTDetails = new SqlDataAdapter();
                da_SCTDetails.SelectCommand = cmd_SCTDetails;
                DataSet dSet_SCTDetails = new DataSet();
                da_SCTDetails.Fill(dSet_SCTDetails);

                logger.Info("cmd_SCTDetails excuted by SqlDataAdapter()");

                int empDetails_count = dSet_SCTDetails.Tables[0].Rows.Count;

                logger.DebugFormat("No. of records found based on the search criteria : {0}",empDetails_count.ToString());

                SCT_Emp result = new SCT_Emp(empDetails_count);

                if (empDetails_count > 0)
                {

                    result.SCT_headerDetails.StatusFlag = 0;
                    result.SCT_headerDetails.StatusMsg = SCT_Constants.Success;

                    int objCounter_item = 0;

                    foreach (DataRow item_dr in dSet_SCTDetails.Tables[0].Rows)
                    {
                        SCT_Emp.childDetails sct_Item = new SCT_Emp.childDetails();
                        sct_Item.EmpID = item_dr["EmployeeNumber"].ToString();
                        sct_Item.EmpName = item_dr["employeeName"].ToString();
                        sct_Item.Grade = item_dr["Grade"].ToString();

                        result.SCT_childDetails[objCounter_item++] = sct_Item;
                    }
                }
                else
                {
                    //Handling : No Match found case

                    result.SCT_headerDetails.StatusFlag = 31;
                    result.SCT_headerDetails.StatusMsg = SCT_Constants.NoMatch;

                    logger.DebugFormat("No rows returned from the table for the search criteria (Name) : {0}", Name_DAL.ToString());
                    logger.Debug("ErrorCode = " + result.SCT_headerDetails.StatusFlag.ToString());
                    logger.Debug("ErrorMessage = " + result.SCT_headerDetails.StatusMsg);

                    return result;
                }

                logger.Debug("In Success case : Status Flag = " + result.SCT_headerDetails.StatusFlag.ToString());
                logger.Debug("In Success case : Status Message = " + result.SCT_headerDetails.StatusMsg);
                logger.Info("Method : searchEmployee_DAL Stop");

                return result;

            }
            catch (SqlException dbEx)
            {
                logger.Fatal("Exception Occured At SCT_DAL - searchEmployee_DAL");
                logger.Debug("Exception Code : " + dbEx.Number.ToString());
                logger.Debug("Exception Description : " + dbEx.Message.ToString());
                logger.Error("Error : searchEmployee_DAL Stop");

                throw dbEx;
            }
            catch (Exception ex)
            {
                logger.Fatal("Exception Occured At SCT_DAL - searchEmployee_DAL  : " + ex.Message.ToString());
                logger.Error("Error : searchEmployee_DAL Stop");

                throw ex;
            }
            finally
            {
                logger.Debug("Connection Status Closed ");

                //conn.Dispose();
            }
        }
Beispiel #2
0
        // <summary>
        /// This Method validates the input parameter for the getPurchaseRequestDetails function
        /// </summary>
        /// <param name="PReqNo_BAL"></param>
        /// <returns></returns>
        /// <history>
        ///     Hari haran      07/05/2012      created
        /// </history>
        public SCT_Emp searchEmployee_BAL(string Name_BAL, string MgrId_BAL, string Flag)
        {
            try
            {
                logger.Info("Method : searchEmployee_BAL Start");

                int validateParam = searchParam(Name_BAL, MgrId_BAL, Flag);

                if ( validateParam == 1 )
                {
                    SCT_Emp sct_Details = new SCT_Emp();
                    sct_Details.SCT_headerDetails.StatusFlag = 21;
                    sct_Details.SCT_headerDetails.StatusMsg = SCT_Constants.ParamNull;

                    logger.Debug("Method searchEmployee_BAL : ErrorCode = " + sct_Details.SCT_headerDetails.StatusFlag.ToString());
                    logger.Debug("Method searchEmployee_BAL : ErrorMessage = " + sct_Details.SCT_headerDetails.StatusMsg);
                    logger.Error("Method : searchEmployee_BAL Stop");

                    return sct_Details;
                }

                SCT_DAL search_DAL = new SCT_DAL();
                return (search_DAL.searchEmployee_DAL(Name_BAL, MgrId_BAL, Flag));
            }
            catch (SqlException dbEx)
            {
                logger.Fatal("Exception  At BAL - searchEmployee_BAL  : " + dbEx.Message.ToString());
                logger.Error("Method : searchEmployee_BAL Stop");
                throw dbEx;
            }
            catch (Exception ex)
            {
                logger.Fatal("Exception  At BAL - searchEmployee_BAL  : " + ex.Message.ToString());
                logger.Error("Method : searchEmployee_BAL Stop");
                throw ex;
            }
        }
Beispiel #3
0
    public SCT_Emp searchEmployee(string Name, string MgrId, string Flag)
    {
        logger.Info("Method : searchEmployee Start");
        logger.DebugFormat("Input parameter Name : {0} ", Name);
        logger.DebugFormat("Input parameter ManagerId : {0} ", MgrId);

        try
        {
            SCT_Emp result = new SCT_Emp();
            SCTInterface search_SI = new SCTInterface();
            result = search_SI.searchEmployee_SI(Name, MgrId, Flag);

            logger.Info("Method : searchEmployee Stop");

            return result;
        }
        catch (SqlException ex)
        {
            webServiceExHandling.ExceptionLog(ex);
            //string mailBody = string.Format(SCT_Constants.mail_BodyFormat,System.DateTime.Now.ToString("F"),PReqNo,ex.TargetSite.ToString(),ex.ToString());
            //webServiceExHandling.Send_Email(SCT_Constants.Email_Dic, mailBody);

            SCT_Emp Error = new SCT_Emp();
            Error.SCT_headerDetails.StatusFlag = ex.Number;
            string expCode = ExpType(ex);
            Error.SCT_headerDetails.StatusMsg = SCT_Constants.cnfgErrMessages[expCode];

            logger.Debug("Return object Error : Status Flag = " + Error.SCT_headerDetails.StatusFlag.ToString());
            logger.Debug("Return object Error : Status Message = " + Error.SCT_headerDetails.StatusMsg);
            logger.Error("Method : searchEmployee Stop");

            return Error;
        }

        catch (Exception ex)
        {
            webServiceExHandling.ExceptionLog(ex);
            //string mailBody = string.Format(SCT_Constants.mail_BodyFormat, System.DateTime.Now.ToString("F"), PReqNo, ex.TargetSite.ToString(), ex.ToString());
            //webServiceExHandling.Send_Email(SCT_Constants.Email_Dic, mailBody);

            SCT_Emp Error = new SCT_Emp();
            Error.SCT_headerDetails.StatusFlag = 1;
            Error.SCT_headerDetails.StatusMsg = SCT_Constants.Error;

            logger.Debug("Return object Error : ErrorCode = " + Error.SCT_headerDetails.StatusFlag.ToString());
            logger.Debug("Return object Error : ErrorMessage = " + Error.SCT_headerDetails.StatusMsg);
            logger.Error("Method : searchEmployee Stop");

            return Error;
        }
    }