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