/// <summary>
/// This Method is a interface to searchCriteria function
/// </summary>
/// <param name="searchCriteria_SI"></param>
/// <returns></returns>
/// <history>
/// Hari haran 07/05/2012 created
/// </history>
public PST_OutputEntity employeeSearch_SI(PST_InputEntity searchCriteria_SI)
{
try
{
logger.Info("Control Flow : Method - employeeSearch_SI Start");
logger.DebugFormat("Employee Name : {0} ",searchCriteria_SI.name);
PST_BAL search_BAL = new PST_BAL();
return (search_BAL.employeeSearch_BAL(searchCriteria_SI));
}
catch (OracleException dbEx)
{
logger.Fatal("Database Exception At PSTInterface - searchCriteria_SI : " + dbEx.Message.ToString());
logger.Error("Control Flow : Method - searchCriteria_SI Stop");
throw dbEx;
}
catch (Exception ex)
{
logger.Fatal("Exception At PSTInterface - searchCriteria_SI : " + ex.Message.ToString());
logger.Error("Control Flow : Method - searchCriteria_SI Stop");
throw ex;
}
}
public PST_OutputEntity employeeSearch(PST_InputEntity searchCriteria)
{
logger.Info("Method : employeeSearch Start");
try
{
PST_OutputEntity result = new PST_OutputEntity();
PSTInterface search_SI = new PSTInterface();
result = search_SI.employeeSearch_SI(searchCriteria);
logger.Info("Method : employeeSearch Stop");
return result;
}
catch (OracleException ex)
{
webServiceExHandling.ExceptionLog(ex);
//string mailBody = string.Format(PST_Constants.mail_BodyFormat,System.DateTime.Now.ToString("F"),transId,ex.TargetSite.ToString(),ex.ToString());
//webServiceExHandling.Send_Email(PST_Constants.Email_Dic, mailBody);
PST_OutputEntity Error = new PST_OutputEntity();
Error.PST_headerDetails.statusFlag = ex.ErrorCode;
string expCode = ExpType(ex);
Error.PST_headerDetails.statusMsg = PST_Constants.cnfgErrMessages[expCode];
logger.Debug("Return object Error : statusFlag = " + Error.PST_headerDetails.statusFlag.ToString());
logger.Debug("Return object Error : statusMsg = " + Error.PST_headerDetails.statusMsg);
logger.Error("Method : employeeSearch Stop");
return Error;
}
catch (Exception ex)
{
webServiceExHandling.ExceptionLog(ex);
//string mailBody = string.Format(PST_Constants.mail_BodyFormat, System.DateTime.Now.ToString("F"), transId, ex.TargetSite.ToString(), ex.ToString());
//webServiceExHandling.Send_Email(PST_Constants.Email_Dic, mailBody);
PST_OutputEntity Error = new PST_OutputEntity();
Error.PST_headerDetails.statusFlag = 1;
Error.PST_headerDetails.statusMsg = PST_Constants.Error;
logger.Debug("Return object Error : statusFlag = " + Error.PST_headerDetails.statusFlag.ToString());
logger.Debug("Return object Error : statusMsg = " + Error.PST_headerDetails.statusMsg);
logger.Error("Method : employeeSearch Stop");
return Error;
}
}
/// <summary>
/// This Method validates the input parameter for the searchCriteria function
/// </summary>
/// <param name="searchCriteria_BAL"></param>
/// <returns></returns>
/// <history>
/// Hari haran 07/05/2012 created
/// </history>
public PST_OutputEntity employeeSearch_BAL(PST_InputEntity searchCriteria_BAL)
{
try
{
logger.Info("Method : searchCriteria_BAL Start");
logger.DebugFormat("Employee Name : {0} ",searchCriteria_BAL.name);
int validate_tsParamFlag = 0;
validate_tsParamFlag = validate_tsParam(searchCriteria_BAL);
if (validate_tsParamFlag == 1)
{
PST_OutputEntity errRes = new PST_OutputEntity();
errRes.PST_headerDetails.statusFlag = 1;
errRes.PST_headerDetails.statusMsg = PST_Constants.Invalid;
logger.Debug("Error in input parameter values");
logger.Debug("ErrorCode = " + errRes.PST_headerDetails.statusFlag.ToString());
logger.Debug("ErrorMessage = " + errRes.PST_headerDetails.statusMsg);
logger.Error("Method : searchCriteria_BAL Stop");
return errRes;
}
else
{
PST_DAL search_DAL = new PST_DAL();
return (search_DAL.employeeSearch_DAL(searchCriteria_BAL,iStart,iStop));
}
}
catch (OracleException dbEx)
{
logger.Fatal("Exception At BAL - searchCriteria_BAL : " + dbEx.Message.ToString());
logger.Error("Method : searchCriteria_BAL Stop");
throw dbEx;
}
catch (Exception ex)
{
logger.Fatal("Exception At BAL - searchCriteria_BAL : " + ex.Message.ToString());
logger.Error("Method : searchCriteria_BAL Stop");
throw ex;
}
}
int validate_tsParam(PST_InputEntity searchCriteria_BAL)
{
int flag = 0;
logger.Info("Method : validate_pstParam Start");
iStart = searchCriteria_BAL.index;
iStop = iStart + 15;
if (searchCriteria_BAL.empNo == 0 && (string.IsNullOrEmpty(searchCriteria_BAL.name)) &&
(string.IsNullOrEmpty(searchCriteria_BAL.sbu)) && (string.IsNullOrEmpty(searchCriteria_BAL.bu)) &&
(string.IsNullOrEmpty(searchCriteria_BAL.jobDesc)) && (string.IsNullOrEmpty(searchCriteria_BAL.location)))
{
flag = 1;
}
if ((!string.IsNullOrEmpty(searchCriteria_BAL.name)) && (searchCriteria_BAL.name.Length < 3) || (searchCriteria_BAL.index < 0) )
{
flag = 1;
}
logger.Info("Method : validate_pstParam Stop");
return flag;
}
/// <summary>
/// This function fetches the PST Details from the database based on Search criteria
/// </summary>
/// <param name="PST_InputEntity"></param>
/// <returns>
/// <paramref name="PST_OutputEntity"/>
/// </returns>
/// <history>
/// Hari haran 07/05/2012 created
/// </history>
public PST_OutputEntity employeeSearch_DAL(PST_InputEntity searchCriteria_Dal,int from ,int to)
{
logger.Info("Method : employeeSearch_DAL Start");
logger.DebugFormat("Employee Name : {0} ", searchCriteria_Dal.name);
databaseLayer dbConStr = new databaseLayer();
string connStr = dbConStr.connectionInfo;
string myConnStr = dbConStr.myConnectionInfo;
logger.Debug("Connection string : " + connStr);
OracleConnection conn = new OracleConnection(connStr);
SqlConnection myConn = new SqlConnection(myConnStr);
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
logger.Debug("Connection Status opened ");
}
logger.Debug("Connection for Image Database : " + myConnStr);
if (myConn.State == System.Data.ConnectionState.Closed)
{
myConn.Open();
logger.Debug("myConnection Status for Image Database opened ");
}
logger.Info("Connetion to the database established");
try
{
string queryString = "Select * from (" +
"SELECT ROW_NUMBER() OVER (ORDER BY Name) AS PA, EMPNO, NAME, GRP, GRADE, DESIG,EMAIL,SEAT,EXT,LOC" +
",PLOT,VOIP,v.ET_PERAREA et_perArea,PERSONAL_AREA,MOBILECC,MOBILESC,MOBILENO,EMPDESIG," +
"OFFPHCC,OFFPHSC,OFFPHNO, jb_desc JobDesc,sbu_description sbu,bu_description bu" +
" FROM v_inhouse_new v,EMP_TRANSACTION,SBU_MASTER,BU_MASTER,JOB_MASTER,EMP_GROUP_IDENTIFY" +
" WHERE ";
if (searchCriteria_Dal.name != string.Empty)
queryString += " lower(NAME) LIKE '%" + searchCriteria_Dal.name.ToLower() + "%' AND";
if (searchCriteria_Dal.bu != string.Empty)
queryString += " lower(et_bu)='" + searchCriteria_Dal.bu.ToLower() + "' AND";
if (searchCriteria_Dal.empNo != 0)
queryString += " et_empno=" + searchCriteria_Dal.empNo + " AND";
if (searchCriteria_Dal.jobDesc != string.Empty)
queryString += " lower(jb_id)='" + searchCriteria_Dal.jobDesc.ToLower() + "' AND";
if (searchCriteria_Dal.location != string.Empty)
queryString += " lower(loc)='" + searchCriteria_Dal.location.ToLower() + "' AND";
if (searchCriteria_Dal.sbu != string.Empty)
queryString += " lower(et_sbu)='" + searchCriteria_Dal.sbu.ToLower() + "' AND";
queryString += " et_empno=empno AND et_sbu=sbu_code AND et_bu=bu_code AND jb_id = eg_job AND eg_empno=empno ORDER BY 'NAME' ASC";
queryString += ")";
OracleCommand cmd_PSTDetails = new OracleCommand();
cmd_PSTDetails.Connection = conn;
cmd_PSTDetails.CommandText = queryString;
//cmd_PSTDetails.CommandType = CommandType.StoredProcedure;
////cmd_PSTDetails.Parameters.AddWithValue("TRANSID", TransactionID);
//logger.Info("cmd_PSTDetails Query parameters initialised");
QueryLog.CmdInfo(cmd_PSTDetails);
OracleDataAdapter da_PSTDetails = new OracleDataAdapter();
da_PSTDetails.SelectCommand = cmd_PSTDetails;
DataSet dSet_PSTDetails = new DataSet();
da_PSTDetails.Fill(dSet_PSTDetails);
logger.Info("cmd_PSTDetails excuted by OracleDataAdapter()");
int empDetails_count = dSet_PSTDetails.Tables[PST_Constants.headerTbNo].Rows.Count;
logger.DebugFormat("No. of records found based on the search criteria :empDetails table {0}", empDetails_count.ToString());
if (to > empDetails_count)
{
to = empDetails_count;
}
int totalObjects = to - from;
if (totalObjects < 0)
{
totalObjects = 0;
}
PST_OutputEntity PST_Details = new PST_OutputEntity(totalObjects);
PST_Details.PST_headerDetails.totalRecords = empDetails_count;
logger.DebugFormat("Object Count : {0}", totalObjects);
if (totalObjects > 0)
{
//DataRow header_dr = dSet_PSTDetails.Tables[PST_Constants.headerTbNo].Rows[0];
PST_Details.PST_headerDetails.statusFlag = 0;
PST_Details.PST_headerDetails.statusMsg = "success";
PST_Details.PST_headerDetails.index = to;
int objCounter_item = 0;
//DataRow img_dr = new DataRow();
//DataRow img_dr1 = dSet_PSTDetails.Tables[PST_Constants.headerTbNo].Rows[0];
//DataRow[] img_dr = img_tb.Select();
//string[] strings = new string[img_dr.Length];
//for (int i = 0; i < strings.Length; i++)
// strings[i] = img_dr[i]["EMPNO"].ToString();
//StringBuilder sb = new StringBuilder();
//sb.Append('(');
//foreach (string i in strings)
//{
// sb.Append(i).Append(',');
//}
//// remove final ,
//sb.Length -= 1;
//sb.Append(')');
//***************************************************************************
//DataTable img_tb = dSet_PSTDetails.Tables[PST_Constants.headerTbNo].DefaultView.ToTable(false, "EMPNO");
//var empIDs = img_tb.Rows.Cast<DataRow>()
//.Select(row => row["EMPNO"].ToString())
//.ToArray();
//string inValue = "(" + String.Join(",", empIDs) + ")";
//string imgQuery = "SELECT * from EmployeeImage WHERE EmpId IN" + inValue;
string imgQuery = "SELECT EmpImg from EmployeeImage WHERE EmpId = @empId";
SqlCommand img_cmd = new SqlCommand();
img_cmd.Connection = myConn;
img_cmd.CommandText = imgQuery;
img_cmd.Parameters.Add("@empId", SqlDbType.VarChar);
//SqlDataAdapter da_imgDetails = new SqlDataAdapter();
//da_imgDetails.SelectCommand = img_cmd;
//DataSet dSet_image = new DataSet();
//da_imgDetails.Fill(dSet_image);
//int a = dSet_image.Tables[0].Rows.Count;
//***************************************************************************
//foreach (DataRow item_dr in dSet_PSTDetails.Tables[PST_Constants.headerTbNo].Rows)
for (int i = from; i < to; i++)
{
PST_OutputEntity.empDetails PST_Emp = new PST_OutputEntity.empDetails();
DataRow item_dr = dSet_PSTDetails.Tables[PST_Constants.headerTbNo].Rows[i];
PST_Emp.empNo = Convert.ToInt32(item_dr["EMPNO"]);
PST_Emp.name = item_dr["Name"].ToString();
PST_Emp.personalArea = item_dr["PERSONAL_AREA"].ToString();
PST_Emp.grp = item_dr["GRP"].ToString();
PST_Emp.grade = item_dr["GRADE"].ToString();
PST_Emp.design = item_dr["DESIG"].ToString();
PST_Emp.email = item_dr["EMAIL"].ToString();
PST_Emp.seat = item_dr["SEAT"].ToString();
PST_Emp.ext = item_dr["EXT"].ToString();
PST_Emp.loc = item_dr["LOC"].ToString();
PST_Emp.plot = item_dr["PLOT"].ToString();
PST_Emp.mobileCC = item_dr["MOBILECC"].ToString();
PST_Emp.mobileSC = item_dr["MOBILESC"].ToString();
PST_Emp.mobileNO = item_dr["MOBILENO"].ToString();
PST_Emp.empDesig = item_dr["EMPDESIG"].ToString();
PST_Emp.voip = item_dr["VOIP"].ToString();
PST_Emp.etPerArea = item_dr["et_perArea"].ToString();
PST_Emp.offPHCC = item_dr["OFFPHCC"].ToString();
PST_Emp.offPHSC = item_dr["OFFPHSC"].ToString();
PST_Emp.offPHNO = item_dr["OFFPHNO"].ToString();
PST_Emp.jobDesc = item_dr["JobDesc"].ToString();
PST_Emp.sbu = item_dr["sbu"].ToString();
PST_Emp.bu = item_dr["bu"].ToString();
img_cmd.Parameters["@empId"].Value = PST_Emp.empNo.ToString();
QueryLog.CmdInfo(img_cmd);
PST_Emp.imageStr = img_cmd.ExecuteScalar().ToString();
//PST_Emp.imageStr = dSet_image.Tables[0].Rows[index++][1].ToString();
PST_Details.PST_child_empDetails[objCounter_item++] = PST_Emp;
}
}
else
{
//Error handling : For No records found
PST_Details.PST_headerDetails.statusFlag = 1;
PST_Details.PST_headerDetails.statusMsg = "No Records Found";
logger.Debug("statusFlag = " + PST_Details.PST_headerDetails.statusFlag.ToString());
logger.Debug("statusMsg = " + PST_Details.PST_headerDetails.statusMsg);
return PST_Details;
}
logger.Debug("In Success case : statusFlag = " + PST_Details.PST_headerDetails.statusFlag.ToString());
logger.Debug("In Success case : statusMsg = " + PST_Details.PST_headerDetails.statusMsg);
logger.Info("Method : employeeSearch_DAL Stop");
//tsTrans.Commit();
return PST_Details;
}
catch (OracleException dbEx)
{
logger.Fatal("Exception Occured At PST_DAL - employeeSearch_DAL");
logger.Debug("Exception Code : " + dbEx.ErrorCode.ToString());
logger.Debug("Exception Description : " + dbEx.Message.ToString());
logger.Error("Error : employeeSearch_DAL Stop");
//tsTrans.Rollback();
throw dbEx;
}
catch (Exception ex)
{
logger.Fatal("Exception Occured At PST_DAL - employeeSearch_DAL : " + ex.Message.ToString());
logger.Error("Error : employeeSearch_DAL Stop");
// tsTrans.Rollback();
//throw new myCustomException(31, ex.Message);
throw ex;
}
finally
{
logger.Debug("Connection Closed ");
conn.Dispose();
myConn.Dispose();
}
}