/********************************************************************************************************/
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();
}
}
//***************************************************************************************************
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;
}
}
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;
}
}