internal static IEnumerable <IEmployeeLoan> getEmployeeLoanByEmployeeId(HRMSEntities db, int employeeId)
{
var result = (from a in db.EmployeeLoans
where a.EmployeeId.Equals(employeeId) && a.IsActive.Equals(true)
join c in db.LoanTypes on a.LoanTypeId equals c.LoanTypeId
join b in db.Companies on a.CompanyId equals b.CompanyId
select new EmployeeLoanModel
{
EmployeeLoanId = a.EmployeeLoanId,
LoanTypeId = a.LoanTypeId,
CompanyId = a.CompanyId,
Reason = a.Reason,
Tenure = a.Tenure,
IsActive = a.IsActive,
DateCreated = a.DateCreated,
EmployeeId = a.EmployeeId,
Amount = a.Amount,
AgreedDate = a.AgreedDate,
DateDisburst = a.DateDisburst,
HRComment = a.HRComment,
IsApproved = a.IsApproved,
InterestRate = a.InterestRate,
PeriodRemain = a.PeriodRemain,
LoanName = c.LoanType1,
CompanyName = b.CompanyName
}).OrderBy(p => p.DateCreated);
return(result);
}
internal static IEnumerable <IEmployeeFeedbackModel> getEmployeeFeedbackList(HRMSEntities db, int companyId)
{
var result = (from d in db.EmployeeFeedbacks
join s in db.Employees on d.EmployeeId equals s.EmployeeId
join q in db.Employees on d.FeedbackOnEmployeeId equals q.EmployeeId
join w in db.Companies on d.CompanyId equals w.CompanyId
join t in db.FeedBacks on d.FeedbackId equals t.FeedbackId
join r in db.Years on t.YearId equals r.YearId
where d.EmployeeId == companyId
select new EmployeeFeedbackModel
{
EmployeeFeedbackId = d.EmployeeFeedbackId,
CompanyId = w.CompanyId,
EmployeeId = s.EmployeeId,
InWhatContext = d.InWhatContext,
FeedbackOnEmployeeId = d.FeedbackOnEmployeeId,
FeedbackOnEmployee = q.LastName + " " + q.FirstName,
DateCreated = d.DateCreated,
FeedbackId = d.FeedbackId,
ProvideOverview = d.ProvideOverview,
DateOfFeedback = d.DateOfFeedback,
Experience = d.Experience,
WhatAreas = d.WhatAreas,
employeeName = s.FirstName + " " + s.LastName,
CompanyName = w.CompanyName,
year = r.Year1
}).ToList();
return(result);
}
public List <EmpSalaryModel> GetEmpSalaryList(string EmpId, string SalaryYear, string SalaryMonth)
{
//connection();
List <EmpSalaryModel> EmpSalList = new List <EmpSalaryModel>();
//string strSql = "select * from EmpSalary where EmpId = " + EmpId;
//if (SalaryYear != "")
//{
// strSql = strSql + " and SalaryYear = " + SalaryYear;
//}
//if (SalaryMonth != "")
//{
// strSql = strSql + " and SalaryMonth = " + SalaryMonth;
//}
//strSql = strSql + " order by SalaryYear desc, SalaryMonth desc";
//SqlCommand com = new SqlCommand(strSql, con);
//com.CommandType = CommandType.Text;
//SqlDataAdapter da = new SqlDataAdapter(com);
//DataTable dt = new DataTable();
//con.Open();
//da.Fill(dt);
//con.Close();
////Bind EmpModel generic list using LINQ
//EmpSalList = (from DataRow dr in dt.Rows
// select new EmpSalaryModel()
// {
// id = Convert.ToInt32(dr["id"]),
// Salary = Convert.ToInt32(dr["Salary"]),
// SalaryMonth = Convert.ToInt32(dr["SalaryMonth"]),
// SalaryYear = Convert.ToInt32(dr["SalaryYear"])
// }).ToList();
HRMSEntities db = new HRMSEntities();
var query = (from c in db.EmpSalaries
where c.EmpId.ToString() == EmpId
orderby c.SalaryYear descending, c.SalaryMonth descending
select new EmpSalaryModel {
id = c.id, Salary = (int)c.Salary, SalaryMonth = (int)c.SalaryMonth, SalaryYear = (int)c.SalaryYear
});
if (SalaryYear != "")
{
query = query.Where(u => u.SalaryYear.ToString() == SalaryYear);
}
if (SalaryMonth != "")
{
query = query.Where(u => u.SalaryMonth.ToString() == SalaryMonth);
}
EmpSalList = query.ToList();
return(EmpSalList);
}
/// <summary>
/// Gets the discipline by identifier.
/// </summary>
/// <param name="db">The database.</param>
/// <param name="disciplineId">The discipline identifier.</param>
/// <returns></returns>
internal static IDiscipline getDisciplineById(HRMSEntities db, int disciplineId)
{
var result = (from a in db.Disciplines
where a.DisciplineId.Equals(disciplineId)
select new Models.DisciplineModel
{
DisciplineId = a.DisciplineId,
EmployeeId = a.EmployeeId,
QueryDate = a.QueryDate,
Offence = a.Offence,
QueryInitiator = a.QueryInitiator,
Investigator = a.Investigator,
Response = a.Response,
QueryStatusId = a.QueryStatusId,
InvestigatorReport = a.InvestigatorReport,
RecommendedSanction = a.RecommendedSanction,
DisciplineCommitteeRecommendation = a.DisciplineCommitteeRecommendation,
ActionTakenId = a.ActionTakenId,
EvidenceDigitalFileId = a.EvidenceDigitalFileId,
DateCreated = a.DateCreated,
CompanyId = a.CompanyId,
IsActive = a.IsActive
}).FirstOrDefault();
return(result);
}
internal static IEnumerable <ICompanyDetail> getCompanyList(HRMSEntities db)
{
var result = (from d in db.Companies
select new CompanyRegistrationModel
{
CompanyId = d.CompanyId,
CompanyAddressLine1 = d.CompanyAddressLine1,
CompanyAddressLine2 = d.CompanyAddressLine2,
CompanyCity = d.CompanyCity,
IsActive = d.IsActive,
CompanyEmail = d.CompanyEmail,
CompanyAlias = d.CompanyAlias,
CompanyCountryId = d.CompanyCountryId,
DateCreated = d.DateCreated,
LogoDigitalFileId = d.LogoDigitalFileId,
CompanyName = d.CompanyName,
CompanyPhone = d.CompanyPhone,
CompanyState = d.CompanyState,
CompanyWebsite = d.CompanyWebsite,
CompanyZipCode = d.CompanyZipCode,
ParentCompanyId = d.ParentCompanyId,
CACRegistrationNumber = d.CACRegistrationNumber
}).Where(x => x.IsActive == true).OrderBy(x => x.CompanyName);
return(result);
}
/// <summary>
/// Gets the training report by identifier.
/// </summary>
/// <param name="db">The database.</param>
/// <param name="trainingReportId">The training report identifier.</param>
/// <returns></returns>
internal static ITrainingReport getTrainingReportById(HRMSEntities db, int trainingReportId)
{
var result = (from s in db.TrainingReports
join x in db.TrainingEvaluationRatings on s.TrainingEvaluationRating equals x.TrainingEvaluationRatingId
join y in db.TrainingEvaluationRatings on s.TrainerEvaluationRating equals y.TrainingEvaluationRatingId
join q in db.TrainingCalenders on s.TrainingCalendarId equals q.TrainingCalenderId
join v in db.Trainings on s.TrainingId equals v.TrainingID
join z in db.Employees on s.EmployeeId equals z.EmployeeId
join e in db.Companies on s.CompanyId equals e.CompanyId
join pdept in db.Trainings on s.TrainingId equals pdept.TrainingID into gj
where s.TrainingReportId.Equals(trainingReportId)
select new Models.TrainingReportModel
{
TrainingReportId = s.TrainingReportId,
DateCreated = s.DateCreated,
TrainingId = s.TrainingId,
TrainingName = v.TrainingName,
CompanyId = s.CompanyId,
CompanyName = e.CompanyName,
EmployeeId = s.EmployeeId,
EmployeeName = z.LastName + " " + z.FirstName,
TrainingCalendarId = s.TrainingCalendarId,
TrainingStarts = q.DeliveryStartDate,
TrainingEnds = q.DeliveryEndDate,
TrainerName = s.TrainerName,
TrainerEvaluationRating = s.TrainerEvaluationRating,
TrainerEvaluationRatingName = y.TrainingEvaluationRating1,
TrainerEvaluationComment = s.TrainerEvaluationComment,
TrainingEvaluationRating = s.TrainingEvaluationRating,
TrainingEvaluationRatingName = x.TrainingEvaluationRating1,
TrainingEvaluationComment = s.TrainerEvaluationComment
}).FirstOrDefault();
return(result);
}
/// <summary>
/// Gets the training report.
/// </summary>
/// <param name="db">The database.</param>
/// <param name="companyId">The company identifier.</param>
/// <returns></returns>
internal static IEnumerable <ITrainingReport> getTrainingReport(HRMSEntities db, int companyId)
{
var result = (from d in db.TrainingReports
where d.CompanyId == companyId
join e in db.Companies on d.CompanyId equals e.CompanyId
join pdept in db.Trainings on d.TrainingId equals pdept.TrainingID
join p in db.TrainingEvaluationRatings on d.TrainerEvaluationRating equals p.TrainingEvaluationRatingId
join m in db.TrainingEvaluationRatings on d.TrainingEvaluationRating equals m.TrainingEvaluationRatingId
join w in db.Employees on d.EmployeeId equals w.EmployeeId
join c in db.TrainingCalenders on d.TrainingCalendarId equals c.TrainingCalenderId
select new TrainingReportModel
{
TrainingReportId = d.TrainingReportId,
TrainingId = pdept.TrainingID,
TrainingCalendarId = c.TrainingCalenderId,
CompanyId = e.CompanyId,
EmployeeId = w.EmployeeId,
TrainerName = w.FirstName,
TrainerEvaluationRating = m.TrainingEvaluationRatingId,
TrainingEvaluationRating = m.TrainingEvaluationRatingId,
TrainerEvaluationComment = d.TrainerEvaluationComment,
TrainingEvaluationComment = d.TrainingEvaluationComment,
DateCreated = d.DateCreated
}).ToList();
return(result);
}
internal static IEnumerable <ILeaveRequestModel> GetLeaveRequestByEmployeeId(HRMSEntities db, int employeeId)
{
var result = (from d in db.LeaveRequests
join s in db.Employees on d.EmployeeID equals s.EmployeeId
join q in db.LeaveStatus on d.LeaveStatusID equals q.LeaveStatusId
join w in db.LeaveTypes on d.LeaveTypeID equals w.LeaveTypeId
where d.EmployeeID.Equals(employeeId)
select new LeaveRequestModel
{
LeaveId = d.LeaveID,
EmployeeId = d.EmployeeID,
EmployeeName = s.FirstName + " " + s.LastName,
LeaveTypeId = d.LeaveTypeID,
LeaveTypeName = w.LeaveTypeName,
DateLeaveStart = d.DateLeaveStart,
DateLeaveEnds = d.DateLeaveEnds,
Comment = d.Comment,
LeaveStatusId = d.LeaveStatusID,
LeaveStatusName = q.Description,
DateRequested = d.DateRequested,
ApprovingAuthorityId = d.ApprovingAuthorityID,
HRApproverId = d.HRApproverID,
ApprovingAuthorityComment = d.ApprovingAuthorityComment,
HRApproverComment = d.HRApproverComment,
DateApprovedDept = d.DateApprovedDept,
DateApprovedHR = d.DateApprovedHR,
DateCreated = d.DateCreated
}).OrderByDescending(x => x.DateCreated);
return(result);
}
public List <StateModel> GetAllStates()
{
/*connection();
* List<StateModel> StateList = new List<StateModel>();
* string strSql = "select * from States order by StateName";
* SqlCommand com = new SqlCommand(strSql, con);
* com.CommandType = CommandType.Text;
* SqlDataAdapter da = new SqlDataAdapter(com);
* DataTable dt = new DataTable();
* con.Open();
* da.Fill(dt);
* con.Close();
*
* //Bind EmpModel generic list using LINQ
* StateList = (from DataRow dr in dt.Rows
*
* select new StateModel()
* {
* id = Convert.ToInt32(dr["id"]),
* StateName = Convert.ToString(dr["StateName"])
* }).ToList();
*
*
* return StateList;*/
HRMSEntities db = new HRMSEntities();
return((from c in db.States
select new StateModel {
id = c.id, StateName = c.StateName
}).ToList());
}
internal static ILeaveRequestModel GetLatestLeaveRequestByEmployeeId(HRMSEntities db, int employeeId)
{
var result = (from d in db.LeaveRequests
join e in db.LeaveTypes on d.LeaveTypeID equals e.LeaveTypeId
where d.EmployeeID.Equals(employeeId)
select new LeaveRequestModel
{
LeaveId = d.LeaveID,
EmployeeId = d.EmployeeID,
LeaveTypeId = d.LeaveTypeID,
DateLeaveStart = d.DateLeaveStart,
DateLeaveEnds = d.DateLeaveEnds,
Comment = d.Comment,
LeaveStatusId = d.LeaveStatusID,
DateRequested = d.DateRequested,
ApprovingAuthorityId = d.ApprovingAuthorityID,
HRApproverId = d.HRApproverID,
ApprovingAuthorityComment = d.ApprovingAuthorityComment,
HRApproverComment = d.HRApproverComment,
DateApprovedDept = d.DateApprovedDept,
DateApprovedHR = d.DateApprovedHR,
DateCreated = d.DateCreated
}).FirstOrDefault();
return(result);
}
internal static IEnumerable <ILeaveRequestModel> GetLeaveRequestByCompanyId(HRMSEntities db, int companyId)
{
var leaveRequests = (from d in db.LeaveRequests
join e in db.Employees on d.EmployeeID equals e.EmployeeId
join r in db.LeaveTypes on d.LeaveTypeID equals r.LeaveTypeId
join y in db.LeaveStatus on d.LeaveStatusID equals y.LeaveStatusId
where e.CompanyId.Equals(companyId)
select new LeaveRequestModel
{
LeaveId = d.LeaveID,
EmployeeId = d.EmployeeID,
LeaveTypeId = d.LeaveTypeID,
DateLeaveStart = d.DateLeaveStart,
DateLeaveEnds = d.DateLeaveEnds,
Comment = d.Comment,
LeaveStatusId = d.LeaveStatusID,
DateRequested = d.DateRequested,
ApprovingAuthorityId = d.ApprovingAuthorityID,
HRApproverId = d.HRApproverID,
ApprovingAuthorityComment = d.ApprovingAuthorityComment,
HRApproverComment = d.HRApproverComment,
DateApprovedDept = d.DateApprovedDept,
DateApprovedHR = d.DateApprovedHR,
DateCreated = d.DateCreated,
LeaveStatusName = y.Description,
LeaveTypeName = r.LeaveTypeName,
EmployeeName = e.LastName + " " + e.FirstName
}).OrderByDescending(x => x.DateCreated);
return(leaveRequests);
}
internal static IEnumerable <ILeaveRequestModel> GetLeaveRequestByEmployeeId(HRMSEntities db, int companyId, int employeeId)
{
var result = (from d in db.LeaveRequests
join e in db.LeaveTypes on d.LeaveTypeID equals e.LeaveTypeId
where d.EmployeeID.Equals(employeeId)
select new LeaveRequestModel
{
LeaveId = d.LeaveID,
EmployeeId = d.EmployeeID,
LeaveTypeId = d.LeaveTypeID,
DateLeaveStart = d.DateLeaveStart,
DateLeaveEnds = d.DateLeaveEnds,
Comment = d.Comment,
LeaveStatusId = d.LeaveStatusID,
DateRequested = d.DateRequested,
ApprovingAuthorityId = d.ApprovingAuthorityID,
HRApproverId = d.HRApproverID,
ApprovingAuthorityComment = d.ApprovingAuthorityComment,
HRApproverComment = d.HRApproverComment,
DateApprovedDept = d.DateApprovedDept,
DateApprovedHR = d.DateApprovedHR,
DateCreated = d.DateCreated
}).OrderByDescending(x => x.LeaveId);
return(result);
}
//==============================================LEAVE REQUESTS==============================//
/// <summary>
/// Gets the leave request by identifier.
/// </summary>
/// <param name="db">The database.</param>
/// <param name="id">The identifier.</param>
/// <returns></returns>
internal static ILeaveRequestModel GetLeaveRequestById(HRMSEntities db, int id)
{
var result = (from d in db.LeaveRequests
join f in db.Employees on d.EmployeeID equals f.EmployeeId
join v in db.LeaveStatus on d.LeaveStatusID equals v.LeaveStatusId
join n in db.LeaveTypes on d.LeaveTypeID equals n.LeaveTypeId
where d.LeaveID.Equals(id)
select new LeaveRequestModel
{
LeaveId = d.LeaveID,
EmployeeId = d.EmployeeID,
LeaveTypeId = d.LeaveTypeID,
DateLeaveStart = d.DateLeaveStart,
DateLeaveEnds = d.DateLeaveEnds,
Comment = d.Comment,
LeaveStatusId = d.LeaveStatusID,
DateRequested = d.DateRequested,
ApprovingAuthorityId = d.ApprovingAuthorityID,
HRApproverId = d.HRApproverID,
ApprovingAuthorityComment = d.ApprovingAuthorityComment,
HRApproverComment = d.HRApproverComment,
DateApprovedDept = d.DateApprovedDept,
DateApprovedHR = d.DateApprovedHR,
DateCreated = d.DateCreated,
EmployeeName = f.LastName + " " + f.FirstName,
LeaveStatusName = v.Description,
LeaveTypeName = n.LeaveTypeName
}).FirstOrDefault();
return(result);
}
internal static IEnumerable <ILeaveRequestModel> getLeaveRequestByCompanyId(HRMSEntities db, int companyId)
{
var result = (from d in db.LeaveRequests
join w in db.Companies on d.CompanyID equals w.CompanyId
join z in db.LeaveStatus on d.LeaveStatusID equals z.LeaveStatusId
where d.EmployeeID.Equals(companyId) && d.IsActive.Equals(true)
select new LeaveRequestModel
{
LeaveId = d.LeaveID,
EmployeeId = d.EmployeeID,
LeaveTypeId = d.LeaveTypeID,
DateLeaveStart = d.DateLeaveStart,
DateLeaveEnds = d.DateLeaveEnds,
Comment = d.Comment,
LeaveStatusId = d.LeaveStatusID,
DateRequested = d.DateRequested,
ApprovingAuthorityId = d.ApprovingAuthorityID,
HRApproverId = d.HRApproverID,
DateApprovedDept = d.DateApprovedDept,
DateCreated = d.DateCreated,
DateApprovedHR = d.DateApprovedHR,
CompanyName = w.CompanyName,
}).OrderBy(x => x.LeaveId);
return(result);
}
/// <summary>
/// Gets the trainings calendar by identifier.
/// </summary>
/// <param name="db">The database.</param>
/// <param name="trainingCalendarId">The training calendar identifier.</param>
/// <returns></returns>
internal static ITrainingCalendar getTrainingsCalendarById(HRMSEntities db, int trainingCalendarId)
{
var result = (from d in db.TrainingCalenders
where d.TrainingCalenderId == trainingCalendarId
join e in db.Companies on d.CompanyId equals e.CompanyId
join r in db.TrainingStatus on d.TrainingStatusId equals r.TrainingStatusId
join pdept in db.Trainings on d.TrainingId equals pdept.TrainingID into gj
from f in gj.DefaultIfEmpty()
select new TrainingCalendarModel
{
TrainingCalenderId = d.TrainingCalenderId,
TrainingId = d.TrainingId,
TrainingName = f.TrainingName,
CompanyId = e.CompanyId,
IsActive = d.IsActive,
TrainingStatusId = d.TrainingStatusId,
DateCreated = d.DateCreated,
Location = d.Location,
DeliveryStartDate = d.DeliveryStartDate,
DeliveryEndDate = d.DeliveryEndDate,
CompanyName = e.CompanyName,
TrainingStatusName = r.Status
}).FirstOrDefault();
return(result);
}
/// <summary>
/// Gets the profileby user identifier.
/// </summary>
/// <param name="db">The database.</param>
/// <param name="userId">The user identifier.</param>
/// <returns></returns>
internal static IProfile getProfilebyUserId(HRMSEntities db, int userId)
{
var result = (from d in db.Profiles
where d.UserId.Equals(userId)
join a in db.Countries on d.CountryId equals a.CountryId
join e in db.Genders on d.GenderId equals e.GenderId
select new ProfileModel
{
ProfileId = d.ProfileId,
UserId = d.UserId,
CountryId = d.CountryId,
Address = d.Address,
DateOfBirth = d.DateOfBirth,
Nationality = a.Name,
Gender = e.Description, //Gender Description
DateCreated = d.DateCreated,
ProfileSummary = d.ProfileSummary,
StateOfOrigin = d.StateOfOrigin,
PictureDigitalFileId = d.PictureDigitalFileId,
CVDigitalFileId = d.CVDigitalFileId,
GenderId = d.GenderId,
}).FirstOrDefault();
return(result);
}
/// <summary>
/// Gets the training analysis by identifier.
/// </summary>
/// <param name="db">The database.</param>
/// <param name="trainingAnalysisId">The training analysis identifier.</param>
/// <returns></returns>
internal static ITrainingNeedAnalysis getTrainingsNeedAnalysisById(HRMSEntities db, int trainingAnalysisId)
{
var result = (from d in db.TrainingNeedAnalysis
where d.TrainingNeedAnaylsisId == trainingAnalysisId
join e in db.Companies on d.CompanyId equals e.CompanyId
join pdept in db.Trainings on d.TrainingId equals pdept.TrainingID
select new TrainingNeedAnalysisModel
{
TrainingNeedAnaylsisId = d.TrainingNeedAnaylsisId,
JobId = d.JobId,
Cost = d.Cost,
CurrencyId = d.CurrencyId,
FrequecyOfDeliveryId = d.FrequecyOfDeliveryId,
TrainingDescription = d.TrainingDescription,
IsProviderApproved = d.IsProviderApproved,
CertificateIssued = d.CertificateIssued,
ApprovedBudget = d.ApprovedBudget,
TrainingDuration = d.TrainingDuration,
TrainingProvider = d.TrainingProvider,
MethodOfDelivery = d.MethodOfDelivery,
ApprovedBudgetCurrency = d.ApprovedBudgetCurrency,
TrainingId = d.TrainingId,
TrainingName = pdept.TrainingName,
CompanyId = e.CompanyId,
IsActive = d.IsActive,
DateCreated = d.DateCreated,
Location = d.Location,
CompanyName = e.CompanyName,
}).FirstOrDefault();
return(result);
}
public ActionResult AgentDelete(int id)
{
Session_CRM sess = (Session_CRM)Session["CRM_Session"];
string msg = "";
AT_Users usr = db.AT_Users.Where(x => x.UserID == id).FirstOrDefault();
try
{
if (usr != null)
{
usr.IsDeleted = true;
usr.UpdateBy = sess.User.UserID;
usr.UpdateDate = DateTime.Now;
db.AT_Users.Attach(usr);
db.UpdateOnly <AT_Users>(usr, x => x.IsDeleted, x => x.UpdateBy, x => x.UpdateDate);
db.SaveChanges();
msg = "User Successfully Deleted.";
}
}
catch (Exception ex)
{
msg = ex.Message;
}
db = new HRMSEntities();
ViewBag.msg = msg;
return(PartialView("_AgentIndex", db.AT_Users.Where(a => a.IsDeleted != true).ToList()));
}
/// <summary>
/// Gets all my training report.
/// </summary>
/// <param name="db">The database.</param>
/// <param name="companyId">The company identifier.</param>
/// <returns></returns>
internal static IEnumerable <ITrainingReport> getAllMyTrainingReport(HRMSEntities db, int companyId)
{
var result = (from d in db.TrainingReports
where d.CompanyId == companyId
join e in db.Employees on d.EmployeeId equals e.EmployeeId
join c in db.Companies on d.CompanyId equals c.CompanyId
join pdept in db.TrainingReports on d.TrainingReportId equals pdept.TrainingReportId into gj
from f in gj.DefaultIfEmpty()
select new TrainingReportModel
{
TrainingReportId = d.TrainingReportId,
DateCreated = d.DateCreated,
TrainingId = d.TrainingId,
CompanyId = d.CompanyId,
EmployeeId = d.EmployeeId,
TrainingCalendarId = d.TrainingCalendarId,
TrainerName = d.TrainerName,
TrainerEvaluationRating = d.TrainerEvaluationRating,
TrainerEvaluationComment = d.TrainerEvaluationComment,
TrainingEvaluationRating = d.TrainingEvaluationRating,
TrainingEvaluationComment = d.TrainerEvaluationComment
}).OrderBy(p => p.TrainingReportId);
return(result);
}
/// <summary>
/// Gets the user list by company identifier.
/// </summary>
/// <param name="db">The database.</param>
/// <param name="companyId">The company identifier.</param>
/// <returns></returns>
internal static IEnumerable <IUserDetail> GetUserListByCompanyId(HRMSEntities db, int companyId)
{
var result = (from d in db.Users
join w in db.Companies on d.CompanyId equals w.CompanyId
where w.CompanyId.Equals(companyId)
select new UserRegistrationModel
{
UserId = d.UserId,
Username = d.Username,
FirstName = d.FirstName,
LastName = d.LastName,
IsActive = d.IsActive,
Email = d.Email,
Password = d.Password,
PhoneNumber = d.PhoneNumber,
DateCreated = d.DateCreated,
IsUserVerified = d.IsUserVerified,
CompanyId = w.CompanyId,
IsResetPassword = d.IsResetPassword,
IsLocked = d.IsLocked,
DateVerified = d.DateVerified,
}).OrderBy(x => x.Username);
return(result);
}
/// <summary>
/// Gets the training with identifier.
/// </summary>
/// <param name="db">The database.</param>
/// <param name="userId">The user identifier.</param>
/// <returns></returns>
internal static IEnumerable <ITrainingHistoryModel> GetTrainingWithId(HRMSEntities db, int userId)
{
var result = (from d in db.TrainingHistories
join x in db.Users on d.UserId equals x.UserId
join w in db.Trainings on d.TrainingId equals w.TrainingID
join p in db.TrainingReports on d.TrainingVendorId equals p.TrainingReportId
join s in db.Certifications on d.CertificationId equals s.CertificateId
where d.UserId.Equals(userId)
select new TrainingHistoryModel
{
TrainingHistoryId = d.TrainingHistoryId,
TrainingNameId = d.TrainingId,
Tname = w.TrainingName,
TrainingVendorId = d.TrainingVendorId,
VendorName = p.TrainerName,
CertificationId = d.CertificationId,
CertificateName = s.Certificate_Name,
Year = d.Year,
UserId = d.UserId,
IsActive = d.IsActive,
UserName = x.LastName + " " + x.FirstName
}).ToList();
return(result);
}
/// <summary>
/// Gets the user by identifier.
/// </summary>
/// <param name="db">The database.</param>
/// <param name="UserId">The user identifier.</param>
/// <returns></returns>
internal static IUserDetail getUserById(HRMSEntities db, int UserId)
{
var result = (from d in db.Users
where d.UserId.Equals(UserId) && d.IsActive.Equals(true)
select new UserRegistrationModel
{
UserId = d.UserId,
Username = d.Username,
FirstName = d.FirstName,
LastName = d.LastName,
IsActive = d.IsActive,
Email = d.Email,
Password = d.Password,
PhoneNumber = d.PhoneNumber,
DateCreated = d.DateCreated,
IsUserVerified = d.IsUserVerified,
CompanyId = d.CompanyId,
IsResetPassword = d.IsResetPassword,
IsLocked = d.IsLocked,
DateVerified = d.DateVerified,
}).FirstOrDefault();
return(result);
}
/// <summary>
/// Gets the training need analysis by identifier.
/// </summary>
/// <param name="db">The database.</param>
/// <param name="trainingNeedAnalysisId">The training need analysis identifier.</param>
/// <returns></returns>
internal static ITrainingNeedAnalysis getTrainingNeedAnalysisById(HRMSEntities db, int trainingNeedAnalysisId)
{
var result = (from s in db.TrainingNeedAnalysis
join x in db.Companies on s.CompanyId equals x.CompanyId
join v in db.Trainings on s.TrainingId equals v.TrainingID
join b in db.JobTitles on s.JobId equals b.JobTitleId
join p in db.FrequencyOfDeliveries on s.FrequecyOfDeliveryId equals p.FrequencyOfDeliveryId
where s.CompanyId.Equals(trainingNeedAnalysisId)
select new Models.TrainingNeedAnalysisModel
{
TrainingNeedAnaylsisId = s.TrainingNeedAnaylsisId,
JobId = s.JobId,
CompanyId = s.CompanyId,
DateCreated = s.DateCreated,
TrainingId = s.TrainingId,
MethodOfDelivery = s.MethodOfDelivery,
IsProviderApproved = s.IsProviderApproved,
Cost = s.Cost,
CurrencyId = s.CurrencyId,
Location = s.Location,
ApprovedBudget = s.ApprovedBudget,
ApprovedBudgetCurrency = s.ApprovedBudgetCurrency,
FrequecyOfDeliveryId = s.FrequecyOfDeliveryId,
CertificateIssued = s.CertificateIssued,
TrainingDescription = s.TrainingDescription
}).FirstOrDefault();
return(result);
}
internal static IRegistration getRegistrationByEmail(HRMSEntities db, string email)
{
var result = (from d in db.Registrations
where d.Email.Equals(email)
select new RegistrationRepModel
{
RegistrationId = d.RegistrationId,
Username = d.Username,
Password = d.Password,
CompanyId = d.CompanyId,
FirstName = d.FirstName,
LastName = d.Lastname,
CompanyName = d.CompanyName,
Email = d.Email,
PhoneNumber = d.PhoneNumber,
AboutUsSourceId = d.AboutUsSourceId,
AboutUsOthers = d.AboutUsOthers,
IsActive = d.IsActive.HasValue ? d.IsActive.Value : false,
IsRegistered = d.IsRegistered,
DateCreated = d.DateCreated,
DateRegistered = d.DateCreated
}).FirstOrDefault();
return(result);
}
internal static ICompanyDetail getCompanyByCACNumber(HRMSEntities db, string CACNumber)
{
var result = (from d in db.Companies
where d.CACRegistrationNumber.Equals(CACNumber)
select new CompanyRegistrationModel
{
CompanyId = d.CompanyId,
CompanyName = d.CompanyName,
CACRegistrationNumber = d.CACRegistrationNumber,
CompanyAddressLine1 = d.CompanyAddressLine1,
CompanyAddressLine2 = d.CompanyAddressLine2,
CompanyCity = d.CompanyCity,
CompanyCountryId = d.CompanyCountryId,
CompanyEmail = d.CompanyEmail,
CompanyPhone = d.CompanyPhone,
CompanyState = d.CompanyState,
CompanyWebsite = d.CompanyWebsite,
CompanyZipCode = d.CompanyZipCode,
DateCreated = d.DateCreated,
ParentCompanyId = d.ParentCompanyId,
CompanyAlias = d.CompanyAlias,
IsActive = d.IsActive,
LogoDigitalFileId = d.LogoDigitalFileId,
}).FirstOrDefault();
return(result);
}
internal static IUser getUserByUserId(HRMSEntities db, int userId)
{
var result = (from d in db.Users
where d.UserId == userId
join e in db.Companies on d.CompanyId equals e.CompanyId into company
from f in company.DefaultIfEmpty()
select new UserModel
{
UserId = d.UserId,
FirstName = d.FirstName,
LastName = d.LastName,
Password = d.Password,
Email = d.Email,
PhoneNumber = d.PhoneNumber,
DateCreated = d.DateCreated,
IsActive = d.IsActive,
IsUserVerified = d.IsUserVerified,
IsLocked = d.IsLocked.HasValue && d.IsLocked.Value,
IsResetPassword = d.IsResetPassword.HasValue && d.IsResetPassword.Value,
Username = d.Username,
CompanyId = d.CompanyId,
CompanyName = f.CompanyName,
DateVerified = d.DateVerified
}).FirstOrDefault();
return(result);
}
internal static IEnumerable <ICompanyDetail> getCompanyForHRList(HRMSEntities db, string username)
{
/* I read through this code again and I remembered that the key assumption made here
* is that one HR admin could be the admin for more than one company, hence I went ahead to get a list
* of companies from the companyAdministrator table.
* I'm not sure if this assumption is correct, but if it isn't, then this doesn't need to retrieve a list of companies
* and if it is, there will be duplication in the event that the same HR is the admin of both a parent company and its child
* TODO: That duplication should be fixed, I'll get to fixing it if it's still here when I'm less busy.
*/
// get the company Ids that are associated with the HR username
var companyIds = (from d in db.CompanyAdministrators
where d.Username == username
select d.CompanyId).ToList();
var companyList = new List <ICompanyDetail>();
// get the company and child company details for each companyId in the companyIds list
foreach (int companyId in companyIds)
{
companyList.AddRange(getMyCompaniesList(db, companyId).ToList());
}
return(companyList);
}
/// <summary>
/// Gets the employee trainings by employee identifier.
/// </summary>
/// <param name="db">The database.</param>
/// <param name="employeeId">The employee identifier.</param>
/// <returns></returns>
internal static IEmployeeTrainingModel getEmployeeTrainingsByEmployeeId(HRMSEntities db, int employeeId)
{
var result = (from d in db.EmployeeTrainings
where d.EmployeeId == employeeId
join e in db.Companies on d.CompanyId equals e.CompanyId
join p in db.Trainings on d.TrainingId equals p.TrainingID
join v in db.Employees on d.EmployeeId equals v.EmployeeId
select new EmployeeTrainingModel
{
EmployeeId = d.EmployeeId,
TrainingId = d.TrainingId,
TrainingName = p.TrainingName,
EmployeeName = v.FirstName,
SupervisorId = d.SupervisorId,
IsApproved = d.IsApproved,
DateApproved = d.DateApproved,
CompanyName = e.CompanyName,
EmployeeTrainingId = d.EmployeeTrainingId,
CompanyId = d.CompanyId,
DateCreated = d.DateCreated,
IsActive = d.IsActive
}).FirstOrDefault();
return(result);
}
public ActionResult Save(AT_Pages peg)
{
string msg = "";
try
{
Session_CRM sess = (Session_CRM)Session["CRM_Session"];
if (peg.PageID > 0)
{
db.AT_Pages.Attach(peg);
db.UpdateExcept <AT_Pages>(peg);
}
else
{
db.Entry(peg).State = System.Data.Entity.EntityState.Added;
}
db.SaveChanges();
msg = "Page saved successfully!";
}
catch (Exception ex)
{
msg = ex.Message;
}
ViewBag.msg = msg;
db = new HRMSEntities();
return(PartialView("_Index", db.AT_Pages.ToList()));
}
internal static IEmployeeLoan getEmployeeLoanByEmployeeandCompanyId(HRMSEntities db, int employeeId, int loanId)
{
var result = (from a in db.EmployeeLoans
where a.EmployeeId.Equals(employeeId) && a.LoanTypeId.Equals(loanId)
select new EmployeeLoanModel
{
EmployeeLoanId = a.EmployeeLoanId,
LoanTypeId = a.LoanTypeId,
CompanyId = a.CompanyId,
Reason = a.Reason,
Tenure = a.Tenure,
IsActive = a.IsActive,
DateCreated = a.DateCreated,
EmployeeId = a.EmployeeId,
Amount = a.Amount,
AgreedDate = a.AgreedDate,
DateDisburst = a.DateDisburst,
HRComment = a.HRComment,
InterestRate = a.InterestRate,
PeriodRemain = a.PeriodRemain,
IsApproved = a.IsApproved
}).FirstOrDefault();
return(result);
}
