/********************************************************************************************************/ 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_RChange getReasonForChange_DAL() { logger.Info("Method : getReasonForChange_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 distinct REASONID, REASONDESC from HRMSAC_REPORTING_CHANGE_REASON"; SqlCommand cmd_SCTDetails = new SqlCommand(); cmd_SCTDetails.Connection = conn; cmd_SCTDetails.CommandText = Query; 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_RChange result = new SCT_RChange(Details_count); if (Details_count > 0) { result.SCT_header.StatusFlag = 0; result.SCT_header.StatusMsg = SCT_Constants.Success; int objCounter_item = 0; foreach (DataRow item_dr in dSet_SCTDetails.Tables[0].Rows) { SCT_RChange.RChange Item = new SCT_RChange.RChange(); Item.ID = item_dr["REASONID"].ToString(); Item.Label = item_dr["REASONDESC"].ToString(); result.SCT_Details[objCounter_item++] = Item; } } else { result.SCT_header.StatusFlag = 1; result.SCT_header.StatusMsg = "No records Found"; } logger.Info("Method : getReasonForChange_DAL Stop"); return result; } catch (SqlException dbEx) { logger.Fatal("Exception Occured At SCT_DAL - getReasonForChange_DAL"); logger.Debug("Exception Code : " + dbEx.Number.ToString()); logger.Debug("Exception Description : " + dbEx.Message.ToString()); logger.Error("Error : getReasonForChange_DAL Stop"); throw dbEx; } catch (Exception ex) { logger.Fatal("Exception Occured At SCT_DAL - getReasonForChange_DAL : " + ex.Message.ToString()); logger.Error("Error : getReasonForChange_DAL Stop"); throw ex; } finally { logger.Debug("Connection Status Closed "); conn.Dispose(); } }
/// <summary> /// This function Updates SCT Details into the Database /// </summary> /// <param name="is_entry_DAL"></param> /// <returns> /// <paramref name="result"/> /// </returns> /// <history> /// Hari haran 08/05/2012 created /// </history> public SCT_UpdateOutputEntity updateSCEmployeeDetails_DAL(SCT_UpdateInputEntity entry_DAL) { logger.Info("Method : updateSCEmployeeDetails_DAL Start"); logger.DebugFormat("Input Parameter entry_BAL : EmpNo value = {0} and WEF = {1}", entry_DAL.EmpNo, entry_DAL.WEF); databaseLayer dbConStr = new databaseLayer(); string connStr = dbConStr.connectionInfo; logger.Debug("Connection string : " + connStr); SqlConnection conn = new SqlConnection(connStr); SqlTransaction Trans = null; if (conn.State == System.Data.ConnectionState.Closed) { conn.Open(); logger.Debug("Connection Status opened "); } logger.Info("Connetion to the database established"); try { SCT_UpdateOutputEntity result = new SCT_UpdateOutputEntity(); SqlCommand insert_cmd = new SqlCommand(); insert_cmd.Connection = conn; insert_cmd.CommandType = CommandType.StoredProcedure; Trans = conn.BeginTransaction("InsertTransaction"); insert_cmd.Transaction = Trans; string insertQuery = "HRMSACInsertRequestChange"; insert_cmd.CommandText = insertQuery; insert_cmd.Parameters.AddWithValue("@EMPNO", Convert.ToInt32(entry_DAL.EmpNo)); insert_cmd.Parameters.AddWithValue("@REASON", entry_DAL.Reason); insert_cmd.Parameters.AddWithValue("@WEF", Convert.ToDateTime(entry_DAL.WEF).ToString()); insert_cmd.Parameters.AddWithValue("@CURRENTSBU", entry_DAL.CurrentBU); insert_cmd.Parameters.AddWithValue("@NEWSBU", entry_DAL.NewBU); insert_cmd.Parameters.AddWithValue("@CURRENTBU", entry_DAL.CurrentSBU); insert_cmd.Parameters.AddWithValue("@NEWBU", entry_DAL.NewSBU); insert_cmd.Parameters.AddWithValue("@CURRENTHORIZONTAL", entry_DAL.CurrentHorizontal); insert_cmd.Parameters.AddWithValue("@NEWHORIZONTAL", entry_DAL.NewHorizontal); insert_cmd.Parameters.AddWithValue("@CURRENTORG", entry_DAL.CurrentOrg); insert_cmd.Parameters.AddWithValue("@NEWORG", entry_DAL.NewOrg); insert_cmd.Parameters.AddWithValue("@CURRENTCOST", entry_DAL.CurrentCost); insert_cmd.Parameters.AddWithValue("@NEWCOST", entry_DAL.NewCost); insert_cmd.Parameters.AddWithValue("@CURRENTPERSONAL", entry_DAL.CurrentPersonal); insert_cmd.Parameters.AddWithValue("@NEWPERSONAL", entry_DAL.NewPersonal); insert_cmd.Parameters.AddWithValue("@CURRENTSUBAREA", entry_DAL.CurrentSubArea); insert_cmd.Parameters.AddWithValue("@NEWSUBAREA", entry_DAL.NewSubArea); insert_cmd.Parameters.AddWithValue("@CURRENTGRADE", entry_DAL.CurrentGrade); insert_cmd.Parameters.AddWithValue("@NEWGRADE", entry_DAL.NewGrade); insert_cmd.Parameters.AddWithValue("@CURRENTSUPPERID", Convert.ToInt32(entry_DAL.CurrentSuperId)); insert_cmd.Parameters.AddWithValue("@NEWSUPPERID", Convert.ToInt32(entry_DAL.NewSuperId)); insert_cmd.Parameters.AddWithValue("@SUBMMITEDCOMMENTS", entry_DAL.SubmittedComment); insert_cmd.Parameters.AddWithValue("@SUBMITTEDBY", Convert.ToInt32(entry_DAL.SubmittedBy)); insert_cmd.Parameters.AddWithValue("@SUBMITTEDFROMIP", DBNull.Value); insert_cmd.Parameters.AddWithValue("@STATUS", "S"); insert_cmd.Parameters.AddWithValue("@SUBMITTEDDATE", System.DateTime.Now.ToString("yyyy-MM-dd H:mm:ss.fff")); insert_cmd.Parameters.AddWithValue("@CURRENTET_PLACE", entry_DAL.CurrBLocation); insert_cmd.Parameters.AddWithValue("@NEWET_PLACE", entry_DAL.NewBLocation); insert_cmd.Parameters.AddWithValue("@REPORTING_TYPE", 'O'); insert_cmd.Parameters.AddWithValue("@CUR_CENTER_DEPLOYMENT", entry_DAL.CurrCDeploy); insert_cmd.Parameters.AddWithValue("@NEW_CENTER_DEPLOYMENT", entry_DAL.NewCDeploy); logger.Info("Inserting Details of SCT UpdateInput Entity"); QueryLog.CmdInfo(insert_cmd); //SqlDataReader dr; int rowsAffected = insert_cmd.ExecuteNonQuery(); //if (dr.HasRows) if (rowsAffected == 1) { //dr.Read(); //if (dr["Return Value"].Equals(0)) //{ //dr.Close(); Trans.Commit(); result.StatusFlag = 0; result.Message = SCT_Constants.Success; logger.Info("Operation : Insert operation executed successfully"); } else { //dr.Close(); Trans.Rollback("InsertTransaction"); logger.Debug("Transaction Rollback Executed"); result.StatusFlag = 1; result.Message = SCT_Constants.Error; logger.Error("Operation : Insert operation Failed"); } return result; } catch (SqlException dbEx) { logger.Fatal("SQL Exception Occured At SCT_DAL - updatePurchaseRequest_DAL : "); logger.Error("Exception Code : " + dbEx.Number.ToString()); logger.Error("Exception Description : " + dbEx.Message.ToString()); logger.Error("Error : updateSCEmployeeDetails_DAL Stop"); try { logger.Debug("Transaction Rollback Executed"); Trans.Rollback("InsertTransaction"); } catch (Exception ex2) { logger.Error("Tranasction Rollback Failed : " + ex2.Message.ToString()); //throw ex2; } throw dbEx; } catch (Exception ex) { logger.Fatal("Exception Occured At SCT_DAL - updateSCEmployeeDetails_DAL : " + ex.Message.ToString()); logger.Error("Method : updateSCEmployeeDetails_DAL Stop"); try { logger.Debug("Transaction Rollback Executed"); Trans.Rollback("InsertTransaction"); } catch (Exception ex2) { logger.Error("Transaction Rollback Failed : "+ex2.Message.ToString()); //throw ex2; } //throw new myCustomException(32, ex.Message); throw ex; } finally { logger.Debug("Connection Status Closed "); conn.Dispose(); } }
/********************************************************************************************************/ public SCT_PSA getPersonnelSubArea_DAL() { logger.Info("Method : getPersonnelSubArea_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 code sa_id,codedescription sa_desc from codes where codetype='personnelsubarea' and displayorder=1"; SqlCommand cmd_SCTDetails = new SqlCommand(); cmd_SCTDetails.Connection = conn; 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 Details_count = dSet_SCTDetails.Tables[0].Rows.Count; logger.DebugFormat("Row Count : ItemDetails table {0}", Details_count.ToString()); SCT_PSA result = new SCT_PSA(Details_count); if (Details_count > 0) { result.SCT_header.StatusFlag = 0; result.SCT_header.StatusMsg = SCT_Constants.Success; int objCounter_item = 0; foreach (DataRow item_dr in dSet_SCTDetails.Tables[0].Rows) { SCT_PSA.PSA Item = new SCT_PSA.PSA(); Item.ID = item_dr["SA_ID"].ToString(); Item.Label = item_dr["SA_DESC"].ToString(); result.SCT_Details[objCounter_item++] = Item; } } else { result.SCT_header.StatusFlag = 1; result.SCT_header.StatusMsg = "No records Found"; } logger.Info("Method : searchEmployee_DAL Stop"); return result; } catch (SqlException dbEx) { logger.Fatal("Exception Occured At SCT_DAL - getPersonnelSubArea_DAL"); logger.Debug("Exception Code : " + dbEx.Number.ToString()); logger.Debug("Exception Description : " + dbEx.Message.ToString()); logger.Error("Error : getPersonnelSubArea_DAL Stop"); throw dbEx; } catch (Exception ex) { logger.Fatal("Exception Occured At SCT_DAL - getPersonnelSubArea_DAL : " + ex.Message.ToString()); logger.Error("Error : getPersonnelSubArea_DAL Stop"); throw ex; } finally { logger.Debug("Connection Status Closed "); conn.Dispose(); } }
/// <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(); } }