public int GetEmployeeID(string EmailId)
{
SqlConnection objConnection = null;
SqlCommand objCommand = null;
string ConnStr = string.Empty;
try
{
ConnStr = DBConstants.GetDBConnectionString();
objConnection = new SqlConnection(ConnStr);
objConnection.Open();
objCommand = new SqlCommand(SPNames.Contract_GetLoggedInEmployeeId, objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
SqlParameter[] sqlParam = new SqlParameter[2];
sqlParam[0] = objCommand.Parameters.AddWithValue("@EmailId", EmailId);
sqlParam[1] = objCommand.Parameters.AddWithValue("@OutEmpId", SqlDbType.Int);
sqlParam[1].Direction = ParameterDirection.Output;
int contract = objCommand.ExecuteNonQuery();
int empId = Convert.ToInt32(sqlParam[1].Value);
return(empId);
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, "Projects.cs", "UpdateProject", EventIDConstants.RAVE_HR_PROJECTS_DATA_ACCESS_LAYER);
}
finally
{
if (objConnection.State == ConnectionState.Open)
{
objConnection.Close();
}
}
}
public static List <KeyValuePair <int, string> > GetAllEmployeeList()
{
DataAccessClass objGetTraining = new DataAccessClass();
List <KeyValuePair <int, string> > employeeList = new List <KeyValuePair <int, string> >();
try
{
objGetTraining.OpenConnection(DBConstants.GetDBConnectionString());
SqlDataReader dr = objGetTraining.ExecuteReaderSP(SPNames.TNI_GetAllActiveEmployeeList);
while (dr.Read())
{
employeeList.Add(new KeyValuePair <int, string>(Convert.ToInt32(dr[DbTableColumn.EMPId]), Convert.ToString(dr[DbTableColumn.EmployeeName])));
}
return(employeeList);
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.InfrastructureLayer, CommonConstants.CommonRepository, "GetAllEmployeeList", EventIDConstants.TRAINING_DATA_ACCESS_LAYER);
}
finally
{
objGetTraining.CloseConncetion();
}
}
public IEnumerable <SelectListItem> FillDesignationList(int DeptId)
{
try
{
DataAccessClass dataAccessClass = new DataAccessClass();
dataAccessClass.OpenConnection(DBConstants.GetDBConnectionString());
SqlParameter[] sqlParam = new SqlParameter[1];
sqlParam[0] = new SqlParameter(SPParameter.DepartmentId, SqlDbType.Int);
sqlParam[0].Value = DeptId;
SqlDataReader objReader = dataAccessClass.ExecuteReaderSP(SPNames.Employee_GetEmployeeDesignations, sqlParam);
SelectListItem selListItem;
List <SelectListItem> newList = new List <SelectListItem>();
newList.Add(new SelectListItem {
Selected = true, Text = "Select", Value = ""
});
while (objReader.Read())
{
//assessmentModel.AssessmentPaper.AssessmentPaperId = Convert.ToInt32(dr[DbTableColumn.AssessmentPaperId]);
selListItem = new SelectListItem()
{
Value = objReader[0].ToString().Trim(), Text = objReader[1].ToString().Trim()
};
newList.Add(selListItem);
}
return(new SelectList(newList, "Value", "Text"));
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, clsEmployeeRepository, "GetDesignation", RMS.Common.Constants.EventIDConstants.TRAINING_DATA_ACCESS_LAYER);
}
}
public List <KeyValuePair <int, string> > GetRMSRoles()
{
DataAccessClass objGetTraining = new DataAccessClass();
List <KeyValuePair <int, string> > RMSRoles = new List <KeyValuePair <int, string> >();
try
{
objGetTraining.OpenConnection(DBConstants.GetDBConnectionString());
//SqlParameter[] sqlParam = new SqlParameter[1];
//sqlParam[0] = new SqlParameter(SPParameter.RaiseID, SqlDbType.Int);
//sqlParam[0].Value = roleid;
SqlDataReader dr = objGetTraining.ExecuteReaderSP(SPNames.USP_TNI_GetRMSRoles);
while (dr.Read())
{
RMSRoles.Add(new KeyValuePair <int, string>(Convert.ToInt32(dr[DbTableColumn.RoleId]), Convert.ToString(dr[DbTableColumn.RoleName])));
}
return(RMSRoles);
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.CommonLayer, "GetRMSRoles", "GetRMSRoles", EventIDConstants.AUTHORIZATION_MANAGER_ERROR);
}
finally
{
objGetTraining.CloseConncetion();
}
}
public static List <Effectiveness> GetMasterTrainingEffectivenessDetails()
{
DataAccessClass daTrainingCourse = new DataAccessClass();
List <Effectiveness> lstEffDtls = new List <Effectiveness>();
try
{
daTrainingCourse.OpenConnection(DBConstants.GetDBConnectionString());
SqlParameter[] sqlParam = new SqlParameter[1];
sqlParam[0] = new SqlParameter("@Category", SqlDbType.VarChar, 50);
sqlParam[0].Value = "TrainingEffectiveness";
SqlDataReader dr = daTrainingCourse.ExecuteReaderSP(SPNames.TNI_GetMasterSP, sqlParam);
while (dr.Read())
{
Effectiveness teff = new Effectiveness();
teff.EffectivenessID = Convert.ToInt32(dr[DbTableColumn.TrainingEffectivenessID]);
teff.EffectivenessName = Convert.ToString(dr[DbTableColumn.TrainingEffectivenessName]);
teff.IsSelected = false;
lstEffDtls.Add(teff);
}
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, clsCommonRepository, "GetTrainingEffectivenessDetails", EventIDConstants.TRAINING_DATA_ACCESS_LAYER);
}
finally
{
daTrainingCourse.CloseConncetion();
}
return(lstEffDtls);
}
//Ishwar Patil : Trainging Module 29/04/2014 : Starts
/// <summary>
/// this function fills the dropdown
/// </summary>
public DataTable FillTraining_DropDownList(string strCategory)
{
try
{
string ConnStr = DBConstants.GetDBConnectionString();
objConnection = new SqlConnection(ConnStr);
objConnection.Open();
objCommand = new SqlCommand(SPNames.TNI_GetMasterSP, objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.Add("@Category", strCategory);
objReader = objCommand.ExecuteReader();
DataTable objDataTable = new DataTable();
objDataTable.Load(objReader);
return(objDataTable);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (objReader != null)
{
objReader.Close();
}
if (objConnection.State == ConnectionState.Open)
{
objConnection.Close();
}
}
}
//Umesh: NIS-changes: Skill Search Report Starts
public BusinessEntities.RaveHRCollection GetPrimaryAndSecondarySkills()
{
objDA = new DataAccessClass();
try
{
BusinessEntities.RaveHRCollection raveHRCollection = new BusinessEntities.RaveHRCollection();
objDA.OpenConnection(DBConstants.GetDBConnectionString());
objDataReader = objDA.ExecuteReaderSP(SPNames.Employee_GetPrimaryAndSecondarySkills);
while (objDataReader.Read())
{
KeyValue <string> keyValue = new KeyValue <string>();
keyValue.KeyName = objDataReader.GetValue(0).ToString();
keyValue.Val = objDataReader.GetValue(0).ToString();
raveHRCollection.Add(keyValue);
}
return(raveHRCollection);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (objDataReader != null)
{
objDataReader.Close();
}
objDA.CloseConncetion();
}
}
// Mohamed : : 29/12/2014 : Ends
#endregion Modified By Mohamed Dangra
/// <summary>
/// Added by Kanchan for the requirment specified in the Discussion with Sawita Kamath and Gaurav Thakkar.
/// Requirment raised:
/// Gives the emailId for the employee whose Employee id is supplied.
/// </summary>
/// <param name="empId"></param>
/// <returns></returns>
public string getEmployeeEmailID(int empId)
{
DataAccessClass objDAForMaster = new DataAccessClass();
SqlParameter[] sqlParam = new SqlParameter[1];
BusinessEntities.MRFDetail mrfDetail = new MRFDetail();
string emailID = string.Empty;
DataSet empDetail = new DataSet();
try
{
//Opens the connection.
objDAForMaster.OpenConnection(DBConstants.GetDBConnectionString());
sqlParam[0] = new SqlParameter(SPParameter.EmpId, DbType.Int32);
sqlParam[0].Value = empId;
empDetail = objDAForMaster.GetDataSet(SPNames.Contract_EmpEmailID, sqlParam);
foreach (DataRow dr in empDetail.Tables[0].Rows)
{
emailID = dr[DbTableColumn.EmailId].ToString();
}
return(emailID);
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, "Master", "getEmployeeEmailID", EventIDConstants.RAVE_HR_MASTER_DATA_ACCESS_LAYER);
}
finally
{
objDAForMaster.CloseConncetion();
}
}
/// <summary>
/// Deallocate the employee from seat.
/// </summary>
/// <returns></returns>
public List <BusinessEntities.SeatAllocation> UnallocatedEmployee()
{
DataAccessClass objDASeatAllocation = new DataAccessClass();
List <BusinessEntities.SeatAllocation> objListSeatAlloctaion = null;
// Initialise Collection class object
raveHRCollection = new BusinessEntities.RaveHRCollection();
try
{
objDASeatAllocation.OpenConnection(DBConstants.GetDBConnectionString());
//--get result
DataSet dsSeatDescription = objDASeatAllocation.GetDataSet(SPNames.SeatAllocation_GetUnallocatedEmployee);
//--Create entities and add to list
BusinessEntities.SeatAllocation objBESeatDetail = null;
objListSeatAlloctaion = new List <BusinessEntities.SeatAllocation>();
foreach (DataRow dr in dsSeatDescription.Tables[0].Rows)
{
objBESeatDetail = new BusinessEntities.SeatAllocation();
if (dr[DbTableColumn.Seat_EmployeeID].ToString() != string.Empty)
{
objBESeatDetail.EmployeeID = Convert.ToInt32(dr[DbTableColumn.Seat_EmployeeID]);
}
if (dr[DbTableColumn.Seat_EmployeeCode].ToString() != string.Empty)
{
objBESeatDetail.EmployeeCode = dr[DbTableColumn.Seat_EmployeeCode].ToString();
}
if (dr[DbTableColumn.Seat_EmployeeName].ToString() != string.Empty)
{
objBESeatDetail.EmployeeName = dr[DbTableColumn.Seat_EmployeeName].ToString();
}
if (dr[DbTableColumn.Seat_EMPEmailID].ToString() != string.Empty)
{
objBESeatDetail.EmployeeEmailID = dr[DbTableColumn.Seat_EMPEmailID].ToString();
}
//--add to list
objListSeatAlloctaion.Add(objBESeatDetail);
}
return(objListSeatAlloctaion);
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, CLASSNAME, "UnallocatedEmployee", EventIDConstants.RAVE_HR_SEATALLOCATION_DATA_ACCESS_LAYER);
}
finally
{
objDASeatAllocation.CloseConncetion();
}
}
/// <summary>
/// this function fills the dropdown
/// </summary>
public DataTable FillDropDownListAccountManager(string strCategory)
{
try
{
string ConnStr = DBConstants.GetDBConnectionString();
objConnection = new SqlConnection(ConnStr);
objConnection.Open();
objCommand = new SqlCommand(SPNames.Contract_GetEmployeeByDesignation, objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.Add(SPParameter.Designation, strCategory);
objCommand.Parameters.Add(SPParameter.FirstID, Convert.ToInt32(MasterEnum.FinanceRole.AccountManagerAM));
objReader = objCommand.ExecuteReader();
DataTable objDataTable = new DataTable();
objDataTable.Load(objReader);
return(objDataTable);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (objReader != null)
{
objReader.Close();
}
if (objConnection.State == ConnectionState.Open)
{
objConnection.Close();
}
}
}
/// <summary>
/// this function fills the dropdown
/// </summary>
public DataTable FillDropDownList(string strCategory)
{
try
{
string ConnStr = DBConstants.GetDBConnectionString();
//string ConnStr = Common.DBConstants.GetDBConnectionString();
int ID = Convert.ToInt32(strCategory);
objConnection = new SqlConnection(ConnStr);
objConnection.Open();
objCommand = new SqlCommand("USP_RaveHR_MasterSP", objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.Add("@Category", ID);
objReader = objCommand.ExecuteReader();
DataTable objDataTable = new DataTable();
objDataTable.Load(objReader);
return(objDataTable);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (objReader != null)
{
objReader.Close();
}
if (objConnection.State == ConnectionState.Open)
{
objConnection.Close();
}
}
}
/// <summary>
/// Gets the relevant experience.
/// </summary>
/// <param name="objGetOrganisationDetails">The obj get organisation details.</param>
/// <returns></returns>
public BusinessEntities.RaveHRCollection GetRelevantExperience(BusinessEntities.OrganisationDetails objGetOrganisationDetails)
{
// Initialise Data Access Class object
objDA = new DataAccessClass();
sqlParam = new SqlParameter[1];
// Initialise Collection class object
raveHRCollection = new BusinessEntities.RaveHRCollection();
try
{
//Open the connection to DB
objDA.OpenConnection(DBConstants.GetDBConnectionString());
sqlParam[0] = new SqlParameter(SPParameter.EmpId, SqlDbType.Int);
if (objGetOrganisationDetails.EMPId == 0)
{
sqlParam[0].Value = DBNull.Value;
}
else
{
sqlParam[0].Value = objGetOrganisationDetails.EMPId;
}
//Execute the SP
objDataReader = objDA.ExecuteReaderSP(SPNames.Employee_GetRelevantExperience, sqlParam);
while (objDataReader.Read())
{
//Initialise the Business Entity object
objOrganisationDetails = new BusinessEntities.OrganisationDetails();
objOrganisationDetails.ExperienceMonth = objDataReader[DbTableColumn.ExperienceInMonth].ToString() == string.Empty ? 0: int.Parse(objDataReader[DbTableColumn.ExperienceInMonth].ToString());
objOrganisationDetails.ExperienceYear = objDataReader[DbTableColumn.ExperienceInYear].ToString() == string.Empty ? 0: int.Parse(objDataReader[DbTableColumn.ExperienceInYear].ToString());
// Add the object to Collection
raveHRCollection.Add(objOrganisationDetails);
}
// Return the Collection
return(raveHRCollection);
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, CLASS_NAME, "GetRelevantExperience", EventIDConstants.RAVE_HR_PROJECTS_DATA_ACCESS_LAYER);
}
finally
{
if (objDataReader != null)
{
objDataReader.Close();
}
objDA.CloseConncetion();
}
}
public DataTable GetNominatedEmployees(int courseId)
{
List <DynamicGrid> objDynamicGridList;
DataAccessClass objDBCon = new DataAccessClass();
objDynamicGridList = new List <DynamicGrid>();
//DynamicGrid objDynamicGrid = null;
DataSet ds = null;
SqlParameter[] sqlParam = new SqlParameter[1];
try
{
objDBCon.OpenConnection(DBConstants.GetDBConnectionString());
sqlParam[0] = new SqlParameter(SPParameter.CourseID, SqlDbType.Int);
sqlParam[0].Value = courseId;
ds = objDBCon.GetDataSet(SPNames.TNI_GetNominatedEmp, sqlParam);
}
catch (RaveHRException ex)
{
throw ex;
}
finally
{
objDBCon.CloseConncetion();
}
return(ds.Tables[0]);
//return objDynamicGridList;
}
public bool SetAssessment(AssessmentPaperModel obj)
{
bool flag = false;
DataAccessClass dataAccessClass = new DataAccessClass();
try
{
dataAccessClass.OpenConnection(DBConstants.GetDBConnectionString());
SqlParameter[] sqlParam = new SqlParameter[3];
sqlParam[0] = new SqlParameter(SPParameter.CourseId, SqlDbType.Int);
sqlParam[0].Value = Int32.Parse(obj.CourseId.ToString());
sqlParam[1] = new SqlParameter(SPParameter.EmployeesId, SqlDbType.Text);
sqlParam[1].Value = Convert.ToString(obj.EmpIdAll.ToString());
sqlParam[2] = new SqlParameter(SPParameter.CreatedBy, SqlDbType.Int);
sqlParam[2].Value = Int32.Parse(obj.CreatedBy.ToString());
SqlDataReader dr = dataAccessClass.ExecuteReaderSP(SPNames.SetAssessment, sqlParam);
while (dr.Read())
{
flag = Convert.ToBoolean(dr[DbTableColumn.Status]);
}
}
catch (Exception ex)
{
throw ex;
}
return(flag);
}
/// <summary>
/// Get employee detail by id
/// </summary>
/// <returns>empid</returns>
public EmployeeModel GetEmployeeDetailByID(int empid)
{
DataAccessClass objGetTraining = new DataAccessClass();
EmployeeModel employeedetail = new EmployeeModel();
try
{
objGetTraining.OpenConnection(DBConstants.GetDBConnectionString());
SqlParameter[] sqlParam = new SqlParameter[1];
sqlParam[0] = new SqlParameter(SPParameter.EmpId, SqlDbType.Int);
sqlParam[0].Value = empid;
SqlDataReader dr = objGetTraining.ExecuteReaderSP(SPNames.TNI_GetEmployeeDetailwithProject, sqlParam);
while (dr.Read())
{
employeedetail.EmpId = Convert.ToInt16(dr[DbTableColumn.EMPId]);
employeedetail.EmployeeName = Convert.ToString(dr[DbTableColumn.EmployeeName]);
employeedetail.EmailID = Convert.ToString(dr[DbTableColumn.EmailId]);
employeedetail.Designation = Convert.ToString(dr[DbTableColumn.Designation]);
employeedetail.PrimarySkill = Convert.ToString(dr[DbTableColumn.PrimarySkills]);
}
return(employeedetail);
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, clsEmployeeRepository, "GetEmployeeDetailByID", EventIDConstants.TRAINING_DATA_ACCESS_LAYER);
}
finally
{
objGetTraining.CloseConncetion();
}
}
public DataTable GetWorkFlowStatus()
{
try
{
string ConnStr = DBConstants.GetDBConnectionString();
objConnection = new SqlConnection(ConnStr);
objConnection.Open();
objCommand = new SqlCommand("USP_Projects_GetWorkFlowStatus", objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
objReader = objCommand.ExecuteReader();
DataTable objDataTable = new DataTable();
objDataTable.Load(objReader);
return(objDataTable);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (objReader != null)
{
objReader.Close();
}
if (objConnection.State == ConnectionState.Open)
{
objConnection.Close();
}
}
}
//19645-Ambar-Start
public void DeleteEmpResumeDetails(string str_deletedfile, int EMPId)
{
try
{
objDA = new DataAccessClass();
objDA.OpenConnection(DBConstants.GetDBConnectionString());
SqlParameter[] sqlParam = new SqlParameter[2];
sqlParam[0] = new SqlParameter(SPParameter.EmpId, SqlDbType.Int);
sqlParam[0].Value = EMPId;
sqlParam[1] = new SqlParameter(SPParameter.FileName, SqlDbType.NVarChar, 100);
sqlParam[1].Value = str_deletedfile;
objDataReader = objDA.ExecuteReaderSP(SPNames.USP_Employee_DelEmployeeResumeDetails, sqlParam);
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, CLASS_NAME, "AddResumeDetails", EventIDConstants.RAVE_HR_EMPLOYEE_DATA_ACCESS_LAYER);
}
finally
{
objDA.CloseConncetion();
}
}
//Umesh: NIS-changes: Skill Search Report Ends
//Siddhesh Arekar Domain Details 09032015 Start
public bool Check_SkillCategory_Exists(string skillCategory)
{
bool isExists = false;
objDA = new DataAccessClass();
sqlParam = new SqlParameter[2];
try
{
objDA.OpenConnection(DBConstants.GetDBConnectionString());
sqlParam[0] = new SqlParameter(SPParameter.SkillCategory, skillCategory);
sqlParam[1] = new SqlParameter(SPParameter.IsSkillExist, isExists);
sqlParam[1].Direction = ParameterDirection.Output;
objDA.ExecuteNonQuerySP(SPNames.CheckSkillCategory, sqlParam);
isExists = (bool)sqlParam[1].Value;
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, CLASS_NAME, "Check_SkillCategory_Exists", EventIDConstants.RAVE_HR_EMPLOYEE_DATA_ACCESS_LAYER);
}
finally
{
objDA.CloseConncetion();
}
return(isExists);
}
/// <summary>
/// Deletes the visa details by emp id.
/// </summary>
/// <param name="EmployeeId">The employee id.</param>
public void DeleteVisaDetailsByEmpId(int EmployeeId)
{
objDA = new DataAccessClass();
sqlParam = new SqlParameter[1];
try
{
objDA.OpenConnection(DBConstants.GetDBConnectionString());
sqlParam[0] = new SqlParameter(SPParameter.EmpId, SqlDbType.Int);
sqlParam[0].Value = EmployeeId;
objDA.ExecuteNonQuerySP(SPNames.Employee_DeleteVisaDetailsByEmpId, sqlParam);
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, CLASS_NAME, Fn_DeleteVisaDetailsByEmpId, EventIDConstants.RAVE_HR_EMPLOYEE_DATA_ACCESS_LAYER);
}
finally
{
objDA.CloseConncetion();
}
}
/// <summary>
/// gets the Seat details as per the ID.
/// </summary>
/// <param name="EmpID"></param>
/// <returns></returns>
public BusinessEntities.SeatAllocation GetSeatDeatilsByID(BusinessEntities.SeatAllocation Seat)
{
DataAccessClass objDASeatAllocation = new DataAccessClass();
try
{
objDASeatAllocation.OpenConnection(DBConstants.GetDBConnectionString());
SqlParameter[] sqlParam = new SqlParameter[1];
sqlParam[0] = new SqlParameter(SPParameter.SeatID, DbType.Int32);
sqlParam[0].Value = Seat.SeatID;
//--get result
DataSet dsSeatDescription = objDASeatAllocation.GetDataSet(SPNames.SeatAllocation_GetSeatDetailsByID, sqlParam);
//--Create entities and add to list
BusinessEntities.SeatAllocation objBESeatDetail = new BusinessEntities.SeatAllocation();
foreach (DataRow dr in dsSeatDescription.Tables[0].Rows)
{
if (dr[DbTableColumn.Seat_SeatName].ToString() != string.Empty)
{
objBESeatDetail.SeatName = dr[DbTableColumn.Seat_SeatName].ToString();
}
if (dr[DbTableColumn.Seat_BayID].ToString() != string.Empty)
{
objBESeatDetail.BayID = Convert.ToInt32(dr[DbTableColumn.Seat_BayID]);
}
if (dr[DbTableColumn.Seat_Description].ToString() != string.Empty)
{
objBESeatDetail.SeatDescription = dr[DbTableColumn.Seat_Description].ToString();
}
if (dr[DbTableColumn.Seat_ExtentionNo].ToString() != string.Empty)
{
objBESeatDetail.ExtensionNo = Convert.ToInt32(dr[DbTableColumn.Seat_ExtentionNo]);
}
if (dr[DbTableColumn.Seat_Landmark].ToString() != string.Empty)
{
objBESeatDetail.SeatLandmark = dr[DbTableColumn.Seat_Landmark].ToString();
}
if (dr[DbTableColumn.Seat_EmployeeID].ToString() != string.Empty)
{
objBESeatDetail.EmployeeID = Convert.ToInt32(dr[DbTableColumn.Seat_EmployeeID].ToString());
}
}
return(objBESeatDetail);
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, CLASSNAME, "GetEmployeeDetailsByID", EventIDConstants.RAVE_HR_SEATALLOCATION_DATA_ACCESS_LAYER);
}
finally
{
objDASeatAllocation.CloseConncetion();
}
}
/// <summary>
/// Updates the organisation details.
/// </summary>
/// <param name="objUpdateVisaDetails">The obj update visa details.</param>
public void UpdateVisaDetails(BusinessEntities.VisaDetails objUpdateVisaDetails)
{
try
{
objDA = new DataAccessClass();
objDA.OpenConnection(DBConstants.GetDBConnectionString());
SqlParameter[] sqlParam = new SqlParameter[5];
sqlParam[0] = new SqlParameter(SPParameter.VisaId, SqlDbType.Int);
sqlParam[0].Value = objUpdateVisaDetails.VisaId;
sqlParam[1] = new SqlParameter(SPParameter.EmpId, SqlDbType.Int);
sqlParam[1].Value = objUpdateVisaDetails.EMPId;
sqlParam[2] = new SqlParameter(SPParameter.CountryName, SqlDbType.NChar, 50);
if (objUpdateVisaDetails.CountryName == "" || objUpdateVisaDetails.CountryName == null)
{
sqlParam[2].Value = DBNull.Value;
}
else
{
sqlParam[2].Value = objUpdateVisaDetails.CountryName;
}
sqlParam[3] = new SqlParameter(SPParameter.VisaType, SqlDbType.NChar, 50);
if (objUpdateVisaDetails.VisaType == "" || objUpdateVisaDetails.VisaType == null)
{
sqlParam[3].Value = DBNull.Value;
}
else
{
sqlParam[3].Value = objUpdateVisaDetails.VisaType;
}
sqlParam[4] = new SqlParameter(SPParameter.ExpiryDate, SqlDbType.SmallDateTime);
if (objUpdateVisaDetails.ExpiryDate == null)
{
sqlParam[4].Value = DBNull.Value;
}
else
{
sqlParam[4].Value = objUpdateVisaDetails.ExpiryDate;
}
int UpdateVisaDetails = objDA.ExecuteNonQuerySP(SPNames.Employee_UpdateVisaDetails, sqlParam);
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, CLASS_NAME, Fn_UpdateVisaDetails, EventIDConstants.RAVE_HR_EMPLOYEE_DATA_ACCESS_LAYER);
}
finally
{
objDA.CloseConncetion();
}
}
/// <summary>
/// Get menu for employee
/// </summary>
/// <returns>menu</returns>
public List <Menu> GetAuthoriseMenuList(int Empid)
{
DataAccessClass daTrainingCourse = new DataAccessClass();
List <Menu> LstMenu = new List <Menu>();
Menu objMenu;
try
{
daTrainingCourse.OpenConnection(DBConstants.GetDBConnectionString());
SqlParameter[] sqlParam = new SqlParameter[1];
sqlParam[0] = new SqlParameter(SPParameter.EmpId, SqlDbType.Int);
sqlParam[0].Value = Empid;
SqlDataReader dr = daTrainingCourse.ExecuteReaderSP(SPNames.GetMenuItemData, sqlParam);
//fetch menu and submenulist
while (dr.Read())
{
objMenu = new Menu();
//objMenu.ResponsibilityID = Convert.ToInt16(dr[DbTableColumn.ResponsibilityID]);
//objMenu.MenuName = Convert.ToString(dr[DbTableColumn.Name]);
objMenu.PageID = Convert.ToInt16(dr[DbTableColumn.PageID]);
objMenu.ParentID = Convert.ToInt16(dr[DbTableColumn.ParentID]);
objMenu.PageName = Convert.ToString(dr[DbTableColumn.PageName]);
objMenu.PageURL = Convert.ToString(dr[DbTableColumn.PageURL]);
objMenu.MenuOrderID = Convert.ToInt16(dr[DbTableColumn.MenuOrderID]);
objMenu.ReportName = Convert.ToString(dr[DbTableColumn.ReportName]);
if (objMenu.ParentID == CommonConstants.ONE && objMenu.PageName == "RMS")
{
objMenu.baseUrl = "";
}
else
{
objMenu.baseUrl = ConfigurationManager.AppSettings[CommonConstants.BaseUrl];
}
//objMenu.SubMenu += LstMenu.Where(m => m.MenuOrderID == objMenu.ParentID).ToList<Menu>();
LstMenu.Add(objMenu);
}
//set submenu
foreach (var menu in LstMenu)
{
menu.SubMenu = LstMenu.Where(m => m.ParentID == menu.MenuOrderID).ToList <Menu>();
}
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, clsCommonRepository, "GetAuthoriseMenuList", EventIDConstants.TRAINING_DATA_ACCESS_LAYER);
}
finally
{
daTrainingCourse.CloseConncetion();
}
return(LstMenu);
}
public string SaveAssessmentResult(DataTable dtResultDetails, AssessmentResult assessmentResult)
{
string totalScore = "";
SqlConnection objConnection = null;
SqlCommand objCommand = null;
string ConnStr = string.Empty;
try
{
ConnStr = DBConstants.GetDBConnectionString();
objConnection = new SqlConnection(ConnStr);
objConnection.Open();
Master master = new Master();
foreach (DataRow row in dtResultDetails.Rows)
{
row["CreatedBy"] = master.GetEmployeeIDByEmailID();
//row["LastEditedBy"] = master.GetEmployeeIDByEmailID();
}
objCommand = new SqlCommand(SPNames.AddAssessmentResult, objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
SqlParameter[] sqlParam = new SqlParameter[10];
sqlParam[0] = objCommand.Parameters.AddWithValue(SPParameter.AssessmentPaperId, assessmentResult.AssessmentPaperId);
sqlParam[1] = objCommand.Parameters.AddWithValue(SPParameter.EmployeeID, assessmentResult.EmployeeId);
sqlParam[2] = objCommand.Parameters.AddWithValue(SPParameter.IsActive, assessmentResult.IsActive);
sqlParam[3] = objCommand.Parameters.AddWithValue(SPParameter.CreatedById, master.GetEmployeeIDByEmailID());
sqlParam[4] = objCommand.Parameters.AddWithValue(SPParameter.CreatedByDate, assessmentResult.CreatedOn);
sqlParam[5] = objCommand.Parameters.AddWithValue(SPParameter.LastModifiedById, master.GetEmployeeIDByEmailID());
sqlParam[6] = objCommand.Parameters.AddWithValue(SPParameter.LastModifiedDate, assessmentResult.LastEditedOn);
sqlParam[7] = objCommand.Parameters.AddWithValue(SPParameter.AssessmentResultDetails, dtResultDetails);
sqlParam[7].SqlDbType = SqlDbType.Structured;
sqlParam[8] = objCommand.Parameters.AddWithValue(SPParameter.TotalScore, 0);
sqlParam[8].Direction = ParameterDirection.Output;
sqlParam[9] = objCommand.Parameters.AddWithValue(SPParameter.Status, 0);
sqlParam[9].Direction = ParameterDirection.Output;
objCommand.ExecuteNonQuery();
totalScore = Convert.ToString(sqlParam[8].Value);
totalScore += "," + Convert.ToString(sqlParam[9].Value);
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, "SaveAssessmentResult", "SaveAssessmentResult", EventIDConstants.TRAINING_DATA_ACCESS_LAYER);
}
finally
{
if (objConnection.State == ConnectionState.Open)
{
objConnection.Close();
}
}
return(totalScore);
}
/// <summary>
///
/// </summary>
/// <param name="addContract"></param>
/// <returns></returns>
public int Save(BusinessEntities.Contract addContract)
{
SqlConnection objConnection = null;
SqlCommand objCommand = null;
string ConnStr = string.Empty;
try
{
ConnStr = DBConstants.GetDBConnectionString();
objConnection = new SqlConnection(ConnStr);
objConnection.Open();
objCommand = new SqlCommand(SPNames.Contract_AddContract, objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
SqlParameter[] sqlParam = new SqlParameter[18];
sqlParam[0] = objCommand.Parameters.AddWithValue(SPParameter.ContractReferenceID, addContract.ContractReferenceID);
sqlParam[1] = objCommand.Parameters.AddWithValue(SPParameter.ContractType, addContract.ContractType);
sqlParam[2] = objCommand.Parameters.AddWithValue(SPParameter.DocumentName, addContract.DocumentName);
sqlParam[3] = objCommand.Parameters.AddWithValue(SPParameter.ContractStatus, addContract.ContractStatus);
sqlParam[4] = objCommand.Parameters.AddWithValue(SPParameter.AccountManagerID, addContract.AccountManagerID);
sqlParam[5] = objCommand.Parameters.AddWithValue(SPParameter.EmailID, addContract.EmailID);
sqlParam[6] = objCommand.Parameters.AddWithValue(SPParameter.ClientName, addContract.ClientName);
sqlParam[7] = objCommand.Parameters.AddWithValue(SPParameter.LocationID, addContract.LocationID);
sqlParam[8] = objCommand.Parameters.AddWithValue(SPParameter.ParentContractID, parentContractId);
sqlParam[9] = objCommand.Parameters.AddWithValue(SPParameter.CreatedById, addContract.CreatedByEmailId);
sqlParam[10] = objCommand.Parameters.AddWithValue(SPParameter.CreatedDate, DateTime.Now);
sqlParam[11] = objCommand.Parameters.AddWithValue(SPParameter.ContractValue, addContract.ContractValue);
sqlParam[12] = objCommand.Parameters.AddWithValue(SPParameter.CurrencyType, addContract.CurrencyType);
sqlParam[13] = objCommand.Parameters.AddWithValue(SPParameter.Division, addContract.Division);
sqlParam[14] = objCommand.Parameters.AddWithValue(SPParameter.Sponsor, addContract.Sponsor);
sqlParam[15] = objCommand.Parameters.AddWithValue(SPParameter.OutContractId, 0);
sqlParam[15].Direction = ParameterDirection.Output;
sqlParam[16] = objCommand.Parameters.AddWithValue(SPParameter.ContractStartDate, addContract.ContractStartDate);
sqlParam[17] = objCommand.Parameters.AddWithValue(SPParameter.ContractEndDate, addContract.ContractEndDate);
int contract = objCommand.ExecuteNonQuery();
int ContractId = Convert.ToInt32(sqlParam[15].Value);
return(ContractId);
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, Contracts, SAVE, EventIDConstants.RAVE_HR_CONTRACT_DATA_ACCESS_LAYER);
}
finally
{
if (objConnection.State == ConnectionState.Open)
{
objConnection.Close();
}
}
}
/// <summary>
/// Gets the seat details.
/// </summary>
/// <param name="objGetContactDetails">The obj get contact details.</param>
/// <returns></returns>
public string GetSeatDetails(BusinessEntities.ContactDetails objGetContactDetails)
{
//Initialise Data Access Class object
objDA = new DataAccessClass();
//Initialise SqlParameter Class object
sqlParam = new SqlParameter[1];
string sname = string.Empty;
try
{
//Open the connection to DB
objDA.OpenConnection(DBConstants.GetDBConnectionString());
//Check each parameters nullibality and add values to sqlParam object accordingly
sqlParam[0] = new SqlParameter(SPParameter.EmpId, SqlDbType.Int);
if (objGetContactDetails.EMPId == 0)
{
sqlParam[0].Value = DBNull.Value;
}
else
{
sqlParam[0].Value = objGetContactDetails.EMPId;
}
objDataReader = objDA.ExecuteReaderSP(SPNames.Employee_GetSeatDetails, sqlParam);
while (objDataReader.Read())
{
sname = objDataReader[DbTableColumn.Seat_SeatName].ToString();
}
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, CLASS_NAME, GETSEATDETAILS, EventIDConstants.RAVE_HR_PROJECTS_DATA_ACCESS_LAYER);
}
finally
{
if (objDataReader != null)
{
objDataReader.Close();
}
objDA.CloseConncetion();
}
// Return the Collection
return(sname);
}
/// <summary>
/// get the branches of a section.
/// </summary>
/// <param name="branchID"></param>
/// <returns></returns>
public RaveHRCollection GetSectionByBranch(int branchID)
{
BusinessEntities.SeatAllocation objSeatAllocation = null;
SqlDataReader objReader = null;;
DataAccessClass objDASeatAllocation = new DataAccessClass();
try
{
raveHRCollection = new RaveHRCollection();
objDASeatAllocation.OpenConnection(DBConstants.GetDBConnectionString());
SqlParameter[] sqlParam = new SqlParameter[1];
sqlParam[0] = new SqlParameter(SPParameter.BranchID, DbType.Int32);
sqlParam[0].Value = branchID;
objReader = objDASeatAllocation.ExecuteReaderSP(SPNames.SeatAllocation_GetSection, sqlParam);
while (objReader.Read())
{
objSeatAllocation = new BusinessEntities.SeatAllocation();
objSeatAllocation.SectionID = int.Parse(objReader[DbTableColumn.Seat_SectionID].ToString());
objSeatAllocation.SectionName = objReader[DbTableColumn.Seat_SectionName].ToString();
raveHRCollection.Add(objSeatAllocation);
}
return(raveHRCollection);
}
//catches RaveHRException exception
catch (RaveHRException ex)
{
throw ex;
}
//catches genral exception
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, CLASSNAME, "GetSectionByBranch", EventIDConstants.RAVE_HR_SEATALLOCATION_DATA_ACCESS_LAYER);
}
//close datareader and connection
finally
{
//checks if datareader is null
if (!objReader.IsClosed)
{
//close datareader
objReader.Close();
}
//close connection
objDASeatAllocation.CloseConncetion();
}
}
/// <summary>
/// Checks the CR reference no.
/// </summary>
/// <param name="objCRDetails">The obj CR details.</param>
/// <returns></returns>
public bool checkCRReferenceNo(BusinessEntities.Contract objCRDetails)
{
bool result = false;
DataAccessClass ProjectDetails = new DataAccessClass();
try
{
ProjectDetails.OpenConnection(DBConstants.GetDBConnectionString());
SqlParameter[] sqlParam = new SqlParameter[4];
sqlParam[0] = new SqlParameter(SPParameter.CRReferenceNo, DbType.String);
sqlParam[0].Value = objCRDetails.CRReferenceNo;
sqlParam[1] = new SqlParameter(SPParameter.ContractId, DbType.Int32);
sqlParam[1].Value = objCRDetails.ContractID;
sqlParam[2] = new SqlParameter(SPParameter.ProjectCode, DbType.String);
sqlParam[2].Value = objCRDetails.CRProjectCode;
sqlParam[3] = new SqlParameter(SPParameter.COUNT, DbType.Int32);
sqlParam[3].Direction = ParameterDirection.Output;
//gets the all employee details related to project .
ProjectDetails.ExecuteNonQuerySP(SPNames.Contracts_CheckCRReferenceNo, sqlParam);
int count = Convert.ToInt32(sqlParam[3].Value);
if (count > 0)
{
result = true;
}
else
{
result = false;
}
return(result);
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, Contracts, "checkCRReferenceNo", EventIDConstants.RAVE_HR_CONTRACT_PRESENTATION_LAYER);
}
finally
{
ProjectDetails.CloseConncetion();
}
}
/// <summary>
/// This method will fetch records from data base and return to business layer
/// </summary>
/// <param name=></param>
/// <returns></returns>
public List <BusinessEntities.ContractProject> GetProjectdetails()
{
List <BusinessEntities.ContractProject> objListOfProjects = null;
DataAccessClass objProjectDetails = new DataAccessClass();
try
{
objProjectDetails.OpenConnection(DBConstants.GetDBConnectionString());
//gets the all project details.
DataSet dsProjectdetails = objProjectDetails.GetDataSet(SPNames.Contract_SearchProjectDetails);
//Create entities and add to list
BusinessEntities.ContractProject objProjects = null;
objListOfProjects = new List <BusinessEntities.ContractProject>();
foreach (DataRow dr in dsProjectdetails.Tables[0].Rows)
{
objProjects = new BusinessEntities.ContractProject();
objProjects.ProjectID = Convert.ToInt32(dr[DbTableColumn.Con_ProjectID]);
objProjects.ProjectCode = dr[DbTableColumn.Con_ProjectCode].ToString();
objProjects.DocumentName = dr[DbTableColumn.Con_DocumentName].ToString();
objProjects.ContractCode = dr[DbTableColumn.Con_ContractCode].ToString();
objProjects.ProjectName = dr[DbTableColumn.Con_ConProjectName].ToString();
objProjects.ContractType = dr[DbTableColumn.Con_ContractType].ToString();
//--add to list
objListOfProjects.Add(objProjects);
}
return(objListOfProjects);
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, CLASSNAME, "GetProjectdetails", EventIDConstants.RAVE_HR_CONTRACT_DATA_ACCESS_LAYER);
}
finally
{
objProjectDetails.CloseConncetion();
}
}
/// <summary>
/// Get_s the client abbrivation.
/// </summary>
/// <param name="MasterId">The master id.</param>
/// <returns></returns>
public string Get_ClientAbbrivation(int MasterId)
{
//Initialise Data Access Class object
objDA = new DataAccessClass();
//Initialise SqlParameter Class object
SqlParameter[] sqlParam = new SqlParameter[1];
string sname = string.Empty;
SqlDataReader objDataReader;
try
{
//Open the connection to DB
objDA.OpenConnection(DBConstants.GetDBConnectionString());
//Check each parameters nullibality and add values to sqlParam object accordingly
sqlParam[0] = new SqlParameter(SPParameter.MasterId, SqlDbType.Int);
if (MasterId == 0)
{
sqlParam[0].Value = DBNull.Value;
}
else
{
sqlParam[0].Value = MasterId;
}
objDataReader = objDA.ExecuteReaderSP(SPNames.Contracts_GetClientAbbrivation, sqlParam);
while (objDataReader.Read())
{
sname = objDataReader[DbTableColumn.Con_Details].ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (objReader != null)
{
objReader.Close();
objDA.CloseConncetion();
}
}
return(sname);
}
/// <summary>
/// Deletes the contact details.
/// </summary>
/// <param name="objDeleteContactDetails">The obj delete contact details.</param>
public void DeleteContactDetails(BusinessEntities.ContactDetails objDeleteContactDetails)
{
//Initialise Data Access Class object
objDA = new DataAccessClass();
//Initialise SqlParameter Class object
sqlParam = new SqlParameter[2];
try
{
//Open the connection to DB
objDA.OpenConnection(DBConstants.GetDBConnectionString());
//Check each parameters nullibality and add values to sqlParam object accordingly
sqlParam[0] = new SqlParameter(SPParameter.EmpContactId, SqlDbType.Int);
if (objDeleteContactDetails.EmployeeContactId == 0)
{
sqlParam[0].Value = DBNull.Value;
}
else
{
sqlParam[0].Value = objDeleteContactDetails.EmployeeContactId;
}
sqlParam[1] = new SqlParameter(SPParameter.EmpId, SqlDbType.Int);
if (objDeleteContactDetails.EMPId == 0)
{
sqlParam[1].Value = DBNull.Value;
}
else
{
sqlParam[1].Value = objDeleteContactDetails.EMPId;
}
//Execute SP along with proper parameters
objDA.ExecuteNonQuerySP(SPNames.Employee_DeleteContact, sqlParam);
}
catch (RaveHRException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new RaveHRException(ex.Message, ex, Sources.DataAccessLayer, CLASS_NAME, DELETECONTACTDETAILS, EventIDConstants.RAVE_HR_PROJECTS_DATA_ACCESS_LAYER);
}
finally
{
objDA.CloseConncetion();
}
}