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