Esempio n. 1
0
        /********************************************************************************************************/
        public SCT_Entity getSCEmployeeDetails_DAL(string empId_DAL,string mgrId_DAL)
        {
            logger.Info("Method : getSCEmployeeDetails_DAL Start");

            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
            {

                string Query = "select EmployeeNumber,employeeName ";
                Query +=",SBUId ,SBU_DESCRIPTION ";
                Query +=",BUId ,BU_Description ";
                Query +=",Horizontalid ,h.divisionname as Horizontal ";
                Query +=",e.DivisionId as OrgId,GM_DESC as OrgDesc ";
                Query +=",PersonnelArea as PAId,pa.codedescription as PADESC ";
                Query +=",ECS_SUB_AREA ,ps.codedescription as SADESC ";
                Query +=",ECS_COST_CENTER ,cc.codedescription as CostDesc ";
                Query +=",c.Codedescription as Grade ";
                Query +=",BaselocationId ,bl.codedescription as BLocDesc ";
                Query +=",CenterofDeployment ,cd.codedescription as CDeployDesc ";

                Query +="from Employees E,Codes C,HRMSAC_EMP_COSTCENTER_SUBAREA,HRMSAC_SBU_MASTER ";
                Query +=",HRMSAC_BU_Master,division H,HRMSAC_GROUP_MASTER O ";
                Query +=",codes pa,codes ps,codes cc,codes bl,codes cd ";

                Query +="where EmployeeNumber=ECS_EMPNO and EmployeeStatus='E'  ";
                Query +="and SBUId = SBU_CODE and SBU_Status='E'  ";
                Query +="and BUId = BU_CODE and BU_Status='E' ";
                Query +="and Horizontalid = h.divisionid and h.divisiontype='HZ' and h.divisionstatus='A' ";
                Query +="and e.DivisionId = o.GM_ID ";
                Query +="and PersonnelArea = pa.code and pa.codetype='personnelarea' ";
                Query +="and ECS_SUB_AREA = ps.code and ps.codetype='personnelsubarea'  ";
                Query +="and c.codetype='grade'  ";
                Query +="and ECS_COST_CENTER = cc.code and cc.codetype='costcenter' ";
                Query +="and BaselocationId = bl.code and bl.codetype='BaseLocation' ";
                Query +="and CenterofDeployment = cd.code and cd.codetype='personnelarea' ";
                Query +="and EmployeeNumber>1000 and CurrentGrade=c.code ";

                Query += "and reportingto=(select top 1 emailid from employees where employeenumber= @mgrId)  ";
                Query += "and EmployeeNumber = @empId";

                SqlCommand cmd_SCTDetails = new SqlCommand();
                cmd_SCTDetails.Connection = conn;
                cmd_SCTDetails.CommandText = Query;
                cmd_SCTDetails.Parameters.AddWithValue("@empId", empId_DAL);
                cmd_SCTDetails.Parameters.AddWithValue("@mgrId", mgrId_DAL);

                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 Details_count = dSet_SCTDetails.Tables[0].Rows.Count;

                logger.DebugFormat("Row Count : ItemDetails table {0}", Details_count.ToString());

                SCT_Entity result = new SCT_Entity();

                if (Details_count > 0)
                {

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

                    DataRow dr = dSet_SCTDetails.Tables[0].Rows[0];

                    result.EmpId = dr["EmployeeNumber"].ToString();

                    result.CurrBUId = dr["BUId"].ToString();
                    result.CurrSBUId = dr["SBUId"].ToString();
                    result.CurrHorizontalId = dr["Horizontalid"].ToString();
                    result.CurrOrgId = dr["OrgId"].ToString();
                    result.CurrCostId = dr["ECS_COST_CENTER"].ToString();
                    result.CurrPersonalId = dr["PAId"].ToString();
                    result.CurrSubAreaId = dr["ECS_SUB_AREA"].ToString();
                    result.CurrBLocId = dr["BaselocationId"].ToString();
                    result.CurrCDeployId = dr["CenterofDeployment"].ToString();

                    result.CurrentBU = dr["BU_Description"].ToString();
                    result.CurrentSBU = dr["SBU_DESCRIPTION"].ToString();
                    result.CurrentHorizontal = dr["Horizontal"].ToString();
                    result.CurrentOrg = dr["OrgDesc"].ToString();
                    result.CurrentCost = dr["CostDesc"].ToString();
                    result.CurrentPersonal = dr["PADESC"].ToString();
                    result.CurrentSubArea = dr["SADESC"].ToString();
                    result.CurrentGrade = dr["Grade"].ToString();
                    result.CurrBLocation = dr["BLocDesc"].ToString();
                    result.CurrCDeploy = dr["CDeployDesc"].ToString();

                }

                else
                {
                    result.StatusFlag = 1;
                    result.StatusMsg = "No records Found";
                }

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

                return result;

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

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

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

                //conn.Dispose();
            }
        }
Esempio n. 2
0
        //***************************************************************************************************
        public SCT_Entity getSCEmployeeDetails_BAL(string empId_BAL, string mgrId_BAL)
        {
            try
            {
                logger.Info("Method : getSCEmployeeDetails_BAL Start");

                if (string.IsNullOrEmpty(empId_BAL) || string.IsNullOrEmpty(mgrId_BAL))
                {
                    SCT_Entity sct_Error = new SCT_Entity();
                    sct_Error.StatusFlag = 21;
                    sct_Error.StatusMsg = SCT_Constants.IdNull;

                    logger.Debug("Method getSCEmployeeDetails_BAL : ErrorCode = " + sct_Error.StatusFlag.ToString());
                    logger.Debug("Method getSCEmployeeDetails_BAL : ErrorMessage = " + sct_Error.StatusMsg);
                    logger.Error("Method : getSCEmployeeDetails_BAL Stop");

                    return sct_Error;
                }
                SCT_DAL get_DAL = new SCT_DAL();
                return (get_DAL.getSCEmployeeDetails_DAL(empId_BAL,mgrId_BAL));
            }
            catch (SqlException dbEx)
            {
                logger.Fatal("Exception  At BAL - getSCEmployeeDetails_BAL  : " + dbEx.Message.ToString());
                logger.Error("Method : getSCEmployeeDetails_BAL Stop");
                throw dbEx;
            }
            catch (Exception ex)
            {
                logger.Fatal("Exception  At BAL - getSCEmployeeDetails_BAL  : " + ex.Message.ToString());
                logger.Error("Method : getSCEmployeeDetails_BAL Stop");
                throw ex;
            }
        }
Esempio n. 3
0
    public SCT_Entity getSCEmployeeDetails(string EmpId, string MgrId)
    {
        logger.Info("Method : getSCEmployeeDetails Start");
        logger.DebugFormat("Input parameter EmployeeId : {0} ", EmpId);
        logger.DebugFormat("Input parameter ManagerId : {0} ", MgrId);

        try
        {
            SCT_Entity result = new SCT_Entity();
            SCTInterface search_SI = new SCTInterface();
            result = search_SI.getSCEmployeeDetails_SI(EmpId, MgrId);

            logger.Info("Method : getSCEmployeeDetails 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_Entity Error = new SCT_Entity();
            Error.StatusFlag = ex.Number;
            string expCode = ExpType(ex);
            Error.StatusMsg = SCT_Constants.cnfgErrMessages[expCode];

            logger.Debug("Return object Error : Status Flag = " + Error.StatusFlag.ToString());
            logger.Debug("Return object Error : Status Message = " + Error.StatusMsg);
            logger.Error("Method : getSCEmployeeDetails 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_Entity Error = new SCT_Entity();
            Error.StatusFlag = 1;
            Error.StatusMsg = SCT_Constants.Error;

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

            return Error;
        }
    }