public List <TrainingVm> BriefByOrgContact(long contactId, long?id = null, int langId = 1) { using (var db = new LMISEntities()) { return(db.TrainingOffers .AsNoTracking() .Where(r => r.IsDeleted == null && r.OrganizationContactID == contactId && (id == null || r.TrainingOfferID == id)) .Select(a => new { Offer = a, TitleDesc = SqlUdf.SubCodeName(a.CourseNameID, langId), Details = a.TrainingOfferDetails }) .ToList() .Select(a => new TrainingVm() { Id = (long)a.Offer.TrainingOfferID, ContactId = (long)a.Offer.OrganizationContactID, PortalUserId = (long)a.Offer.PortalUsersID, Title = a.TitleDesc, Status = a.Offer.trainingStatus, Approval = (Approval)a.Offer.Is_Approved, RejectReason = a.Offer.RejectReason, NewTitle = a.Details.Select(d => new LocalString(d.LanguageID, d.OtherCours_Name)).ToList(), }) .ToList()); } }
public List <JobVm> Search(string keywords, int langId = 1) { var lookup = keywords.ToLower().Split(' '); using (var db = new LMISEntities()) { return(db.JobOffers .AsNoTracking() .Where(r => r.IsDeleted == null && r.JobStatus && r.IsApproved == (byte)Approval.Approved) .Select(a => new { Offer = a, TitleDesc = SqlUdf.SubCodeName(a.JobTiltleID, langId), TitleSearchable = SqlUdf.SubCodeSearchString(a.JobTiltleID, langId), Details = a.JobOfferDetails }) .Where(r => lookup.Any(q => r.TitleSearchable.ToLower().Contains(q))) .ToList() .Select(a => new JobVm { JobId = (long)a.Offer.JobOfferID, Title = a.TitleDesc, Description = (new GlobalString(a.Details.Select(d => new LocalString(d.LanguageID, d.JobDescription)).ToList())).Reduce(langId), EmploymentType = a.Offer.EmploymentTypeID, Country = a.Offer.CountryID, City = a.Offer.CityID }) .ToList()); } }
public List <JobVm> BriefByOrgContact(long contactId, long?id = null, int langId = 1) { using (var db = new LMISEntities()) { return(db.JobOffers .AsNoTracking() .Where(r => r.IsDeleted == null && r.OrganizationContactID == contactId && (id == null || r.JobOfferID == id)) .Select(a => new { Offer = a, TitleDesc = SqlUdf.SubCodeName(a.JobTiltleID, langId), Details = a.JobOfferDetails }) .ToList() .Select(a => new JobVm() { JobId = (long)a.Offer.JobOfferID, ContactId = (long)a.Offer.OrganizationContactID.GetValueOrDefault(), PortalUserId = (long)a.Offer.PortalUsersID, Title = a.TitleDesc, JobStatus = a.Offer.JobStatus, Approval = (Approval)a.Offer.IsApproved, RejectReason = a.Offer.RejectReason, NewTitle = a.Details.Select(d => new LocalString(d.LanguageID, d.OtherJobTitle)).ToList(), PostDate = a.Offer.PostDate }) .ToList()); } }
public Dictionary <string, object> ApplicationRequirements(long id, int langId = 1) { using (var db = new LMISEntities()) { return(db.JobOffers .AsNoTracking() .Where(r => r.JobOfferID == id && r.IsDeleted == null && r.JobStatus && r.IsApproved == (byte)Approval.Approved) .Select(a => new { Title = SqlUdf.SubCodeName(a.JobTiltleID, langId), AppTemplate = a.JobOfferAdditionalDocs .Where(d => d.AdditionalDocTypeID == "99999999") .Select(d => d.AdditionalDocTemplatePath) .FirstOrDefault(), AdditionalDocs = a.JobOfferAdditionalDocs .Where(d => d.AdditionalDocTypeID != "99999999") .Select(d => new CodeSet { id = d.AdditionalDocTypeID, desc = SqlUdf.SubCodeName(d.AdditionalDocTypeID, langId) }).ToList() }) .ToList() .Select(a => new Dictionary <string, object> { { "Id", id }, { "Title", a.Title }, { "AppTemplate", a.AppTemplate }, { "AdditionalDocs", a.AdditionalDocs } }) .SingleOrDefault()); } }
public List <Infrastructure.Data.Entities.HelpfulLinkVm> Get(int languageId, decimal?HelpfulLinkURLId) { List <HelpfulLinkVm> result; using (var db = new LMISEntities()) { result = db.HelpfulLinks.Where( w => w.IsDeleted == null && (w.HelpfulLinkLanguage == languageId || languageId == 0) && w.HelpfulLinkID == (HelpfulLinkURLId > 0 ? HelpfulLinkURLId : w.HelpfulLinkID)) .Select(s => new HelpfulLinkVm { HelpfulLinkID = s.HelpfulLinkID, HelpfulLinkURL = s.HelpfulLinkURL, HelpfulLinkName = s.HelpfulLinkName, HelpfulLinkLanguage = s.HelpfulLinkLanguage, GroupID = s.GroupID, GroupName = SqlUdf.SubCodeName(s.GroupID, languageId) }) .OrderBy(o => o.GroupName) .ToList(); } return(result); }
public Dictionary <string, object> Review(long id, int langId = 1) { using (var db = new LMISEntities()) { return(db.Feedbacks .AsNoTracking() .Where(r => r.FeedbackID == id && r.IsDeleted == null) .Select(a => new { Type = SqlUdf.SubCodeName(a.FeedbackTypeId, 1), Feedback = a }) .ToList() .Select(a => new Dictionary <string, object> { { "Id", id }, { "Type", a.Type }, { "Title", a.Feedback.Title }, { "Desc", a.Feedback.Description }, { "Lang", a.Feedback.FeedbackLang }, { "Approval", a.Feedback.IsReviewed ? (byte)Approval.Approved : (byte)Approval.Pending } }) .SingleOrDefault()); } }
public List <object> GroupSkillsForIndustry(string industryId, int langId = 1) { List <object> ds; using (var db = new LMISEntities()) { ds = db.Skills.Where(a => a.IndustryId == industryId) .Select(a => a.SkillID) .Distinct() .GroupJoin( db.Skills.Where(a => a.IndustryId == industryId), a => a, b => b.SkillID, (k, g) => new { SkillId = k, SkillDesc = SqlUdf.SubCodeName(k, langId), Levels = g.Select(a => new { id = a.SkillLevel_ID, desc = SqlUdf.SubCodeName(a.SkillLevel_ID, langId) }), TypeIsRequired = (SqlUdf.SubCodeParent(k) == "02000003") //For Lingual Skills Only }) .ToList() .GroupJoin( db.SubCodes.Where(a => a.GeneralID == "022") //Skill Types .Select(a => new { id = a.SubID, desc = SqlUdf.SubCodeName(a.SubID, langId) }) .Distinct(), a => true, b => true, (k, g) => new { id = k.SkillId, desc = k.SkillDesc, Types = k.TypeIsRequired ? g.ToList() : g.ToList().TakeWhile(a => false), k.Levels }) .SelectMany(a => a.Types.DefaultIfEmpty(new { id = "", desc = "" }), (p, c) => new { id = industryId + "|" + p.id + "|" + c.id, desc = p.desc + (c.id == "" ? "" : " [" + c.desc + "]"), options = p.Levels.Select(a => new { id = industryId + "|" + p.id + "|" + c.id + "|" + a.id, desc = a.desc + ": " + p.desc + (c.id == "" ? "" : " [" + c.desc + "]"), Skill = new { p.id, p.desc }, Type = (c.id == "" ? null : c), Level = a }) }) .ToList() .Cast <object>().ToList(); } return(ds); }
public List <JobVm> ListByOrgContact(long contactId, long?id = null, int langId = 1) { using (var db = new LMISEntities()) { var q = db.JobOffers .AsNoTracking() .Where(r => r.IsDeleted == null && (contactId == -1 || r.OrganizationContactID == contactId) && (id == null || r.JobOfferID == id)) .Select(r => new { JobOffer = r, Skills = r.jobOfferSkillsDetails.Select(a => new JobVm.JobSkill { IsNew = false, Industry = new CodeSet { id = a.IndustryID, desc = SqlUdf.SubCodeName(a.IndustryID, langId) }, Level = new CodeSet { id = a.SkillLevelID, desc = SqlUdf.SubCodeName(a.SkillLevelID, langId) }, Skill = new CodeSet { id = a.SkillID, desc = SqlUdf.SubCodeName(a.SkillID, langId) }, Type = new CodeSet { id = a.SkillTypeID, desc = SqlUdf.SubCodeName(a.SkillTypeID, langId) } }), OtherSkills = r.JobOtherSkills.Select(a => new JobVm.JobSkill { IsNew = true, Industry = new CodeSet { id = a.IndustryId, desc = SqlUdf.SubCodeName(a.IndustryId, langId) }, Level = new CodeSet { id = a.SkillLevelId, desc = SqlUdf.SubCodeName(a.SkillLevelId, langId) }, Skill = new CodeSet { id = null, desc = a.OtherSkill }, Type = new CodeSet { id = null, desc = null } }) }); return(q.ToList().Select(r => MapJobOffers(r.JobOffer, r.Skills, r.OtherSkills)).ToList()); } }
public List <SkillsInformationVm.TrainingSkill> SkillsList(UserInfo user, int langId = 1) { using (var db = new LMISEntities()) { var Skills = db.IndividualSkillsDetails.Where(r => r.PortalUsersID == user.PortalUserId).Select(a => new SkillsInformationVm.TrainingSkill { IsNew = false, Industry = new CodeSet { id = a.IndustryID, desc = SqlUdf.SubCodeName(a.IndustryID, langId) }, Level = new CodeSet { id = a.SkillLevelID, desc = SqlUdf.SubCodeName(a.SkillLevelID, langId) }, Skill = new CodeSet { id = a.SkillID, desc = SqlUdf.SubCodeName(a.SkillID, langId) }, Type = new CodeSet { id = a.SkillTypeID, desc = SqlUdf.SubCodeName(a.SkillTypeID, langId) }, YOfExperience = a.YearsOf_Experience }); var OtherSkills = db.IndividualOtherSkills.Where(r => r.PortalUsersID == user.PortalUserId).Select(a => new SkillsInformationVm.TrainingSkill { IsNew = true, Industry = new CodeSet { id = a.IndustryId, desc = SqlUdf.SubCodeName(a.IndustryId, langId) }, Level = new CodeSet { id = a.SkillLevelId, desc = SqlUdf.SubCodeName(a.SkillLevelId, langId) }, Skill = new CodeSet { id = null, desc = a.OtherSkill }, Type = new CodeSet { id = null, desc = null } // YOfExperience = a.YearsOf_Experience }); var TrainingSkills = new List <SkillsInformationVm.TrainingSkill>(); var allTrainingSkills = TrainingSkills.Concat(Skills) .Concat(OtherSkills) .ToList(); // TrainingSkills.Add(Skills); return(allTrainingSkills.ToList()); } }
public List <EducationalInformationVm> EducationList(UserInfo user) { // List<IndividualEducationlevel> ds; using (var db = new LMISEntities()) { var EducationList = db.IndividualEducationlevels.Where(r => r.IsDeleted == null && r.PortalUsersID == user.PortalUserId).Select(r => new EducationalInformationVm() { IndividualEducationlevelID = (long)r.IndividualEducationlevelID, EducationName = r.IndividualEducationlevelDets.FirstOrDefault().InstitutionName, EducationalLevelId = r.LevelOfEducation.Trim(), EducationType = r.IndividualEducationlevelDets.FirstOrDefault().InstitutionType, EducationalLevelName = SqlUdf.SubCodeName("00" + r.LevelOfEducation.Trim(), 1) }); var c = EducationList.ToList(); return(EducationList.ToList()); } }
public List <object> ListIndustriesHavingSkills(int langId = 1) { List <object> ds; using (var db = new LMISEntities()) { ds = db.SubCodes .Where(r => r.GeneralID == "096" && db.Skills.Select(s => s.IndustryId).Distinct().Contains(r.SubID)) .Select(a => new { id = a.SubID, desc = SqlUdf.SubCodeName(a.SubID, langId) }) .ToList() .Cast <object>().ToList(); } return(ds); }
public List <RequestLogVm> ListRequestLog() { using (var db = new LMISEntities()) { return(db.RequestLogs .AsNoTracking() .Select(r => new { RequestLog = r, db.Admins.FirstOrDefault(a => a.AdminId == r.AdminID).AdminName, PortalUserName = SqlUdf.PortalUserName(r.PortalUserID, 1), RequestTypeDesc = SqlUdf.SubCodeName(r.RequestType, 1), Title = SqlUdf.RequestTitle(r.RequestType, r.RequestID, 1), DeleteDate = SqlUdf.RequestDeleteDate(r.RequestType, r.RequestID) }) .ToList() .Select(r => new RequestLogVm { Id = (long)r.RequestLog.ID, Admin = new CodeSet { id = r.RequestLog.AdminID, desc = r.AdminName }, PortalUserId = (long)r.RequestLog.PortalUserID, PortalUserName = r.PortalUserName, RequestType = new CodeSet { id = r.RequestLog.RequestType, desc = r.RequestTypeDesc }, RequestId = (long)r.RequestLog.RequestID, Title = r.Title, PostDate = r.RequestLog.PostDate, UpdateDate = r.RequestLog.UpdateDate, DeleteDate = r.DeleteDate, Approval = (Approval)r.RequestLog.Is_Approved }) .ToList()); } }
public List <TrainingVm> Search(string keywords, int langId = 1) { var lookup = keywords.ToLower().Split(' '); using (var db = new LMISEntities()) { return(db.TrainingOffers .AsNoTracking() .Where(r => r.IsDeleted == null && r.trainingStatus && r.Is_Approved == (byte)Approval.Approved) .Select(a => new { Offer = a, TitleDesc = SqlUdf.SubCodeName(a.CourseNameID, langId), TitleSearchable = SqlUdf.SubCodeSearchString(a.CourseNameID, langId), Details = a.TrainingOfferDetails, OrgNames = a.OrganizationDetail.OrganizationDetails_Det .Select(r => new { r.LanguageID, r.OrganizationName }).ToList() }) .Where(r => lookup.Any(q => r.TitleSearchable.ToLower().Contains(q))) .ToList() .Select(a => new TrainingVm { Id = (long)a.Offer.TrainingOfferID, Title = a.TitleDesc, Description = (new GlobalString(a.Details.Select(d => new LocalString(d.LanguageID, d.CourseDescription)).ToList())).Reduce(langId), PortalUserId = (long)a.Offer.PortalUsersID, Country = a.Offer.CountryID, City = a.Offer.CityID, Extras = new Dictionary <string, object> { { "OrgName", new GlobalString(a.OrgNames.Select(d => new LocalString(d.LanguageID, d.OrganizationName)).ToList()) .ToLocalString((Language)langId, true).T } } }) .ToList()); } }
public Dictionary <string, List <CodeSet> > FillSkillsByIndustryAndLevel(List <string> filters, int langId = 1) { Dictionary <string, List <CodeSet> > ds; using (var db = new LMISEntities()) { ds = db.Skills.Where(a => filters.Contains(a.IndustryId + "|" + a.SkillLevel_ID)) .Select(a => new { Filter = a.IndustryId + "|" + a.SkillLevel_ID, SkillId = a.SkillID, SkillDesc = SqlUdf.SubCodeName(a.SkillID, langId) }) .ToList() .GroupBy(a => a.Filter) .ToDictionary(g => g.Key, g => g.Select(a => new CodeSet { id = a.SkillId, desc = a.SkillDesc }).ToList()); } return(ds); }
public List <Infrastructure.Data.Entities.FaqVm> Get(int languageId, decimal?Id) { List <FaqVm> result; using (var db = new LMISEntities()) { result = db.FAQs.Where(w => !w.IsDeleted && w.FAQID == (Id > 0 ? Id : w.FAQID) && w.FAQLanguage == (languageId > 0 ? languageId : w.FAQLanguage)) .Select(s => new FaqVm { FAQID = s.FAQID, FAQCategoryID = s.FAQCategoryID, Question = s.Question, Answer = s.Answer, FAQLanguage = s.FAQLanguage, GroupName = SqlUdf.SubCodeName(s.FAQCategoryID, languageId) }).ToList(); } return(result.OrderBy(o => o.GroupName).ToList()); }
public List <Infrastructure.Data.Entities.QualificationsVm> Get(int languageId, decimal?Id) { List <QualificationsVm> result; using (var db = new LMISEntities()) { result = db.Qualifications.Where(w => w.IsDeleted == null && w.Id == (Id > 0 ? Id : w.Id)) .Select(s => new QualificationsVm { Id = s.Id, GroupID = s.GroupID, QualificationAr = s.QualificationAr, QualificationEn = s.QualificationEn, QualificationFr = s.QualificationFr, GroupName = SqlUdf.SubCodeName(s.GroupID, languageId) }).ToList(); } return(result.OrderBy(o => o.GroupName).ToList()); }
public List <JobsCountVm> JobsPerYear(bool started, bool jobStatus, int langId = 1) { using (var db = new LMISEntities()) { var result = db.JobOffers .Where(r => r.IsDeleted == null && (r.JobStatus || jobStatus == false) && r.IsApproved == (byte)Approval.Approved && (r.StartDate.Year == DateTime.Now.Year || r.EndDate.Year == DateTime.Now.Year || started == false)) .Select(a => new JobsCountVm { id = a.JobOfferID, Title = SqlUdf.SubCodeName(a.JobTiltleID, langId), StartDate = a.StartDate, EndDate = a.EndDate }).ToList(); return(result); } }
public EventVm Get(long id, int langId = 1) { using (var db = new LMISEntities()) { return(db.Events .Include(r => r.EventsDetails) .Where(r => r.IsDeleted == null && r.EventId == id) .Select(r => new { e = r, TypeStr = SqlUdf.SubCodeName(r.EventTypeID, langId) }) .ToList() .Select(r => new EventVm() { EventId = (long)r.e.EventId, ContactId = (long)r.e.OrganizationContactID, Title = r.e.EventsDetails.Select(d => new LocalString(d.LanguageID, d.EventTitle)).ToList(), Address = r.e.EventsDetails.Select(d => new LocalString(d.LanguageID, d.EventAddress)).ToList(), StartDate = r.e.StartDate.AsUtc(), EndDate = r.e.EndDate.AsUtc(), Type = r.e.EventTypeID, TypeStr = r.TypeStr, Price = r.e.Price, ContactAddress = r.e.EventsDetails.Select(d => new LocalString(d.LanguageID, d.EventContactAddress)).ToList(), ContactTelephone = r.e.EventContactTelephone, ContactWebsite = r.e.EventContactWebsite, FilePath = r.e.UploadPath, IsInternal = r.e.IsInternal == true, IsInformal = r.e.IsInformal == true, Approval = (Approval)r.e.IsApproved, RejectReason = r.e.RejectReason }) .SingleOrDefault()); } }
public OrganizationProfileVM GetProfile(long portalUserId, int langId = 1) { OrganizationProfileVM ret; using (var db = new LMISEntities()) { var orgObj = db.OrganizationDetails.Where(org => org.PortalUsersID == portalUserId); ret = orgObj.Select(org => new OrganizationProfileVM() { #region Load main object with translation PortalUsersID = (long)org.PortalUsersID, OrganizationLogoPath = org.OrganizationLogoPath, OrganizationSize = org.OrganizationSize, Country = SqlUdf.SubCodeName(org.CountryID, langId), City = SqlUdf.SubCodeName(org.CityID, langId), ZipPostalCode = org.ZipPostalCode, Telephone = org.Telephone, OrganizationWebsite = org.OrganizationWebsite, OrganizationProfilePath = org.OrganizationProfilePath, EconomicActivity = SqlUdf.SubCodeName(org.EconomicActivity, langId), IndustryType = SqlUdf.SubCodeName(org.IndustryType, langId), YearsofExperience = SqlUdf.SubCodeName(org.YearsofExperienceID, langId), EstablishmentDate = org.EstablishmentDate, RegistrationNumberWithITC = org.RegistrationNumberWithITC, Is_Approved = org.Is_Approved, IsDiscalaimerApproved = org.IsDiscalaimerApproved, PostDate = org.PostDate, Approval = (Approval)org.Is_Approved, RejectReason = org.RejectReason, #endregion #region Portal user data IDType = SqlUdf.SubCodeName(org.PortalUser.IDType, langId), IDNumber = org.PortalUser.IDNumber, UserCategory = SqlUdf.SubCodeName(org.PortalUser.UserCategory, langId), UserSubCategory = SqlUdf.SubCodeName(org.PortalUser.UserSubCategory, langId), TrainingProvider = org.PortalUser.TrainingProvider, Employer = org.PortalUser.Employer, TrainingSeeker = org.PortalUser.TrainingSeeker #endregion }).Single(); #region load localized values var det = orgObj.Single() .OrganizationDetails_Det.Where(d => d.PortalUsersID == portalUserId).ToList(); ret.GS = new Dictionary <string, GlobalString> { { "OrgName", new GlobalString(det.Select(d => new LocalString(d.LanguageID, d.OrganizationName)).ToList()) }, { "Address", new GlobalString(det.Select(d => new LocalString(d.LanguageID, d.Address)).ToList()) }, { "OtherIndustry", new GlobalString(det.Select(d => new LocalString(d.LanguageID, d.OtherIndustryType)).ToList()) } }; ret.OrganizationName = ret.GS["OrgName"].ToLocalString((Language)langId, true).T; ret.Address = ret.GS["Address"].ToLocalString((Language)langId, true).T; ret.OtherIndustryType = ret.GS["OtherIndustry"].ToLocalString((Language)langId, true).T; #endregion } return(ret); }
public TrainingVm View(long id, long?portalUserId, int langId = 1) { using (var db = new LMISEntities()) { return(db.TrainingOffers .AsNoTracking() .Where(r => r.TrainingOfferID == id && r.IsDeleted == null && r.trainingStatus && r.Is_Approved == (byte)Approval.Approved) .Select(r => new { Offer = r, Details = r.TrainingOfferDetails, Title = SqlUdf.SubCodeName(r.CourseNameID, langId), Country = SqlUdf.SubCodeName(r.CountryID, langId), City = SqlUdf.SubCodeName(r.CityID, langId), Occurrence = r.TrainingOfferOccurrences.Select(d => SqlUdf.SubCodeName(d.OccurrenceID, langId)).ToList(), TimeZone = SqlUdf.SubCodeName(r.TimeZone, langId), Skills = r.TrainingSkillDetails.Select(a => new TrainingVm.TrainingSkill { Industry = new CodeSet { id = a.IndustryID, desc = SqlUdf.SubCodeName(a.IndustryID, langId) }, Level = new CodeSet { id = a.SkillLevelID, desc = SqlUdf.SubCodeName(a.SkillLevelID, langId) }, Skill = new CodeSet { id = a.SkillID, desc = SqlUdf.SubCodeName(a.SkillID, langId) }, Type = new CodeSet { id = a.SkillTypeID, desc = SqlUdf.SubCodeName(a.SkillTypeID, langId) } }).ToList(), OrgNames = r.OrganizationDetail.OrganizationDetails_Det .Select(a => new { a.LanguageID, a.OrganizationName }).ToList(), OrgUrl = r.OrganizationDetail.OrganizationWebsite, Applied = r.TrainingApplies.Any(a => a.IndPortalUserID == portalUserId), }) .ToList() .Select(r => new TrainingVm { Id = (long)r.Offer.TrainingOfferID, FileName = r.Offer.OutlineFile, Title = r.Title, Description = (new GlobalString(r.Details.Select(d => new LocalString(d.LanguageID, d.CourseDescription)).ToList())).Reduce(langId), Country = r.Country, City = r.City, Address = (new GlobalString(r.Details.Select(d => new LocalString(d.LanguageID, d.Address)).ToList())).Reduce(langId), Duration = r.Offer.CourseDurationPerDay, StartDate = r.Offer.StartDate.AsUtc(), EndDate = r.Offer.EndDate.AsUtc(), Seats = r.Offer.NoOfSeats, Cost = r.Offer.CourseCost, Occurrence = r.Occurrence, TimeFrom = r.Offer.CourseTimeFrom.AsUtc(), TimeTo = r.Offer.CourseTimeTo.AsUtc(), TimeZone = r.TimeZone, Skills = r.Skills, Extras = new Dictionary <string, object> { { "OrgName", new GlobalString(r.OrgNames.Select(d => new LocalString(d.LanguageID, d.OrganizationName)).ToList()) .ToLocalString((Language)langId, true).T }, { "OrgUrl", r.OrgUrl }, { "Applied", r.Applied } } }) .SingleOrDefault()); } }
public JobVm View(long id, long?portalUserId, int langId = 1) { using (var db = new LMISEntities()) { return(db.JobOffers .AsNoTracking() .Where(r => r.JobOfferID == id && r.IsDeleted == null && r.JobStatus && r.IsApproved == (byte)Approval.Approved) .Select(r => new { Offer = r, Details = r.JobOfferDetails, Title = SqlUdf.SubCodeName(r.JobTiltleID, langId), FileName = r.JobOfferAdditionalDocs .Where(d => d.AdditionalDocTypeID == "99999999") .Select(d => d.AdditionalDocTemplatePath).FirstOrDefault(), EmploymentType = SqlUdf.SubCodeName(r.EmploymentTypeID, langId), EdLevel = r.JobOfferEducationLevels .Select(d => SqlUdf.SubCodeName(d.EducationLevelID, langId)).FirstOrDefault(), EdCert = r.JobOfferEducationLevels.FirstOrDefault().JobOfferEducationLevelDetails, Gender = SqlUdf.SubCodeName(r.GenderID, langId), Country = SqlUdf.SubCodeName(r.CountryID, langId), City = SqlUdf.SubCodeName(r.CityID, langId), Skills = r.jobOfferSkillsDetails.Select(a => new JobVm.JobSkill { Industry = new CodeSet { id = a.IndustryID, desc = SqlUdf.SubCodeName(a.IndustryID, langId) }, Level = new CodeSet { id = a.SkillLevelID, desc = SqlUdf.SubCodeName(a.SkillLevelID, langId) }, Skill = new CodeSet { id = a.SkillID, desc = SqlUdf.SubCodeName(a.SkillID, langId) }, Type = new CodeSet { id = a.SkillTypeID, desc = SqlUdf.SubCodeName(a.SkillTypeID, langId) } }).ToList(), Currency = SqlUdf.SubCodeName(r.SalaryCurrencyID, langId), MedConsitions = r.jobOfferMedicalDetails .Select(d => SqlUdf.SubCodeName(d.MedicalID, langId)).ToList(), DocTypes = r.JobOfferAdditionalDocs .Where(d => d.AdditionalDocTypeID != "99999999") .Select(d => SqlUdf.SubCodeName(d.AdditionalDocTypeID, langId)).ToList(), OrgNames = r.OrganizationDetail.OrganizationDetails_Det .Select(a => new { a.LanguageID, a.OrganizationName }).ToList(), OrgUrl = r.OrganizationDetail.OrganizationWebsite, Applied = r.JobApplieds.Any(a => a.IndPortalUserID == portalUserId), }) .ToList() .Select(r => new JobVm { JobId = (long)r.Offer.JobOfferID, Title = r.Title, Description = (new GlobalString(r.Details.Select(d => new LocalString(d.LanguageID, d.JobDescription)).ToList())).Reduce(langId), FileName = r.FileName, ExpFrom = r.Offer.ExpYearFrom, ExpTo = r.Offer.ExpYearTo, Vacancies = r.Offer.NumberOfVacanciesPosition, StartDate = r.Offer.StartDate.AsUtc(), EndDate = r.Offer.EndDate.AsUtc(), EmploymentType = r.EmploymentType, EdLevel = r.EdLevel, EdCert = (new GlobalString(r.EdCert.Select(d => new LocalString(d.LanguageID, d.CertificationType)).ToList())).Reduce(langId), Gender = r.Gender, Country = r.Country, City = r.City, Skills = r.Skills, PerMonth = r.Offer.SalaryRangePerMonth, PerHour = r.Offer.SalaryRangePerHour.GetValueOrDefault(), Currency = r.Currency, MedConditions = r.MedConsitions, DocTypes = r.DocTypes, Extras = new Dictionary <string, object> { { "OrgName", new GlobalString(r.OrgNames.Select(d => new LocalString(d.LanguageID, d.OrganizationName)).ToList()) .ToLocalString((Language)langId, true).T }, { "OrgUrl", r.OrgUrl }, { "Applied", r.Applied }, { "Express", string.IsNullOrWhiteSpace(r.FileName) && r.DocTypes.Count == 0 } } }) .SingleOrDefault()); } }
public List <Dictionary <string, object> > Search(long jobOfferId, int langId = 1) { using (var db = new LMISEntities()) { var filter = jobOfferId > 0; var reqSkills = new List <string>(); var reqEdLevels = new List <string>(); var ret = new List <Dictionary <string, object> >(); if (filter) { var offer = db.JobOffers.AsNoTracking() .Where(a => a.JobOfferID == jobOfferId) .Select(a => new { expFrom = a.ExpYearFrom, expTo = a.ExpYearTo, skills = a.jobOfferSkillsDetails, medConds = a.jobOfferMedicalDetails, edLevels = a.JobOfferEducationLevels }) .ToList() .Select(a => new { a.expFrom, a.expTo, skills = a.skills.Select(dr => dr.SkillID).Distinct().ToList(), medConds = a.medConds.Select(dr => dr.MedicalID).ToList(), edLevels = a.edLevels.Select(dr => dr.EducationLevelID).Distinct().ToList() }) .Single(); reqSkills = offer.skills; reqEdLevels = offer.edLevels; ret.Add(new Dictionary <string, object>() { { "Filters", new { ExpFrom = offer.expFrom, ExpTo = offer.expTo, Skills = offer.skills, MedConds = offer.medConds, EdLevels = offer.edLevels } } }); } ret.AddRange(db.IndividualDetails .AsNoTracking() .Where(r => r.Is_Approved == (byte)Approval.Approved) .Select(a => new { Candidate = a, Details = a.IndividualDetailsDets, Experience = a.IndividualExperienceDetails.Where(dr => dr.IsDeleted == null && (dr.CurrentEmploymentStatus == 1 || dr.EmploymentEndDate.HasValue)) .Select(dr => new { days = SqlFunctions.DateDiff("DAY", dr.EmploymentStartDate, dr.CurrentEmploymentStatus == 1 ? DateTime.Today : dr.EmploymentEndDate) ?? 0, job = dr.EmploymentJobTitle }), Skills = a.IndividualSkillsDetails.Where(dr => dr.IsDeleted == null) .Select(dr => new { id = dr.SkillID, desc = SqlUdf.SubCodeName(dr.SkillID, langId) }), CountryDesc = SqlUdf.SubCodeName(a.CountryID, langId), CityDesc = SqlUdf.SubCodeName(a.CityID, langId), GenderDesc = SqlUdf.SubCodeName(a.GenderId, langId), MedCondDesc = SqlUdf.SubCodeName(a.IndividualMedicalID, langId), EdLevels = a.IndividualEducationlevels.Where(dr => dr.IsDeleted == null) .Select(dr => new { id = dr.LevelOfEducation, desc = SqlUdf.SubCodeName(dr.LevelOfEducation, langId) }) }) .ToList() .Select(a => new Dictionary <string, object> { { "Id", a.Candidate.PortalUsersID }, { "FirstName", (new GlobalString(a.Details.Select(d => new LocalString(d.LanguageID, d.FirstName)).ToList())).ToLocalString((Language)langId, true).T }, { "LastName", (new GlobalString(a.Details.Select(d => new LocalString(d.LanguageID, d.LastName)).ToList())).ToLocalString((Language)langId, true).T }, { "Experience", a.Experience.Select(e => e.days).Sum() }, { "Jobs", a.Experience.Select(e => e.job).Distinct().ToList() }, { "SkillRating", reqSkills.Any() ? a.Skills.Select(dr => reqSkills.Contains(dr.id)).Distinct().Count() / reqSkills.Count() : -1 }, { "Skills", a.Skills.Select(s => s.id).Distinct().ToList() }, { "Country", new CodeSet { id = a.Candidate.CountryID, desc = a.CountryDesc } }, { "City", new CodeSet { id = a.Candidate.CityID, desc = a.CityDesc } }, { "Gender", new CodeSet { id = a.Candidate.GenderId, desc = a.GenderDesc } }, { "MedCond", new CodeSet { id = a.Candidate.IndividualMedicalID, desc = a.MedCondDesc } }, { "EdLevels", a.EdLevels.Select(s => s.id).Distinct().ToList() }, { "EdlevelMatching", !reqEdLevels.Any() || a.EdLevels.Any(dr => reqEdLevels.Contains(dr.id)) } }) .ToList()); return(ret); } }
public List <CalendarVM> List(int langId) { List <CalendarVM> result; List <Event> resultEvent; List <Opportunity> resultOpportunity; using (var db = new LMISEntities()) { resultEvent = db.Events.Include(r => r.EventsDetails).Where(r => r.IsDeleted == null && r.IsApproved == 2 && r.StartDate.Year >= DateTime.Now.Year).ToList(); resultOpportunity = db.Opportunities.Include(r => r.OpportunitiesDetails).Where(r => r.IsDeleted == null && r.Is_Approved == 2 && r.StartDate.Year >= DateTime.Now.Year).ToList(); var resultTraning = db.TrainingOffers.Where(r => r.IsDeleted == null && r.Is_Approved == 2 && r.StartDate.Year >= DateTime.Now.Year).Select(s => new { s.TrainingOfferID, Title = SqlUdf.SubCodeName(s.CourseNameID, langId), s.StartDate, s.EndDate }).ToList(); result = resultEvent.Select(r => new CalendarVM { Id = r.EventId, Title = r.EventsDetails.Where(w => w.LanguageID == langId).Select(d => d.EventTitle).FirstOrDefault(), StartDate = r.StartDate.AsUtc(), EndDate = r.EndDate.AsUtc(), Type = 1 }).ToList(); result.AddRange(resultOpportunity.Select(r => new CalendarVM { Id = r.OpportunityID, Title = r.OpportunitiesDetails.Where(w => w.LanguageID == langId).Select(d => d.OpportunityTitle).FirstOrDefault(), StartDate = r.StartDate.AsUtc(), EndDate = r.EndDate.AsUtc(), Type = 2 }).ToList()); result.AddRange(resultTraning.Select(r => new CalendarVM { Id = r.TrainingOfferID, Title = r.Title, StartDate = r.StartDate.AsUtc(), EndDate = r.EndDate.AsUtc(), Type = 3 }).ToList()); } return(result); }
public IndividualProfileVM GetProfile(long portalUserId, int langId = 1) { IndividualProfileVM indObj; using (var db = new LMISEntities()) { var portalObj = db.PortalUsers.Where(pu => pu.PortalUsersID == portalUserId); indObj = portalObj.Select(user => new IndividualProfileVM() { #region Load main object with translation PortalUsersID = (long)user.PortalUsersID, FullName = user.IndividualDetail.IndividualDetailsDets.Where(t => t.LanguageID == langId) .Select(d => d.FirstName + " " + d.LastName) .FirstOrDefault(), AddressLocalized = user.IndividualDetail.IndividualDetailsDets.Where(t => t.LanguageID == langId) .Select(d => d.Address) .FirstOrDefault(), Email = user.IndividualDetail.Email, MobileNo = user.IndividualDetail.MobileNo, TelephoneNo = user.IndividualDetail.TelephoneNo, Gender = SqlUdf.SubCodeName(user.IndividualDetail.GenderId, langId), DateOfBirth = user.IndividualDetail.DateOfBirth, MaritalStatus = SqlUdf.SubCodeName(user.IndividualDetail.MaritalStatusId, langId), MilitaryStatus = SqlUdf.SubCodeName(user.IndividualDetail.MilitaryStatus_Id, langId), Nationality = SqlUdf.SubCodeName(user.IndividualDetail.NationalityId, langId), Country = SqlUdf.SubCodeName(user.IndividualDetail.CountryID, langId), City = SqlUdf.SubCodeName(user.IndividualDetail.CityID, langId), IndividualMedical = SqlUdf.SubCodeName(user.IndividualDetail.IndividualMedicalID, langId), AllowtoViewMyInfo = (user.IndividualDetail.AllowtoViewMyInfo == 1) ? true : false, GenderId = user.IndividualDetail.GenderId, MaritalStatusId = user.IndividualDetail.MaritalStatusId, MilitaryStatus_Id = user.IndividualDetail.MilitaryStatus_Id, NationalityId = user.IndividualDetail.NationalityId, IndividualMedicalID = user.IndividualDetail.IndividualMedicalID, CountryID = user.IndividualDetail.CountryID, CityID = user.IndividualDetail.CityID, IDType = user.IDType, IDNumber = user.IDNumber, PhotoPath = user.IndividualDetail.PhotoPath, Approval = (Approval)user.IndividualDetail.Is_Approved, RejectReason = user.IndividualDetail.RejectReason, #endregion #region Educationzl Info Educations = user.IndividualDetail.IndividualEducationlevels.Where(w => w.IsDeleted == null) .Select(r => new IndividualProfileEducationVM() { IndividualEducationlevelID = (long)r.IndividualEducationlevelID, EducationName = r.IndividualEducationlevelDets.Where(rDet => rDet.LanguageID == langId) .FirstOrDefault() .InstitutionName, FacultyID = r.IndividualEducationlevelDets.Where(rDet => rDet.LanguageID == langId) .FirstOrDefault() .FacultyName, LevelOfEducation = r.LevelOfEducation.Trim(), InstitutionType = r.IndividualEducationlevelDets.Where(rDet => rDet.LanguageID == langId) .FirstOrDefault() .InstitutionType, LevelOfEducationName = SqlUdf.SubCodeName(r.LevelOfEducation.Trim(), 1), GraduationYear = r.GraduationYear, Grade = r.IndividualEducationlevelDets.Where(rDet => rDet.LanguageID == langId) .FirstOrDefault() .Grade, Degree = r.Degree, GradeGPA = r.GradeGPA }).ToList(), #endregion #region Experience Info Experiences = user.IndividualDetail.IndividualExperienceDetails.Where(w => w.IsDeleted == null) .Select(x => new IndividualProfileExperienceVM() { IndividualExperienceID = (long)x.IndividualExperienceID, Name = x.IndividualExperienceDetails_Det.FirstOrDefault(l => l.LanguageID == langId) .EmployerName, JobDescription = x.IndividualExperienceDetails_Det.FirstOrDefault(l => l.LanguageID == langId) .EmploymentJobDescription, EmploymentStartDate = x.EmploymentStartDate, EmploymentEndDate = x.EmploymentEndDate, ExpYears = x.ExpYears, EmploymentJobTitle = SqlUdf.SubCodeName(x.EmploymentJobTitle, langId), ExpMonths = x.ExpMonths }).OrderByDescending(o => o.EmploymentStartDate).ToList(), #endregion #region Skill Info Skills = user.IndividualDetail.IndividualSkillsDetails.Select(s => new IndividualProfileSkillVM() { IndividualSkillsDetailsID = (long)s.IndividualSkillsDetailsID, Skill = new CodeSet { id = s.SkillID, desc = SqlUdf.SubCodeName(s.SkillID, langId) }, Industry = new CodeSet { id = s.IndustryID, desc = SqlUdf.SubCodeName(s.IndustryID, langId) }, SkillLevel = new CodeSet { id = s.SkillLevelID, desc = SqlUdf.SubCodeName(s.SkillLevelID, langId) }, SkillType = new CodeSet { id = s.SkillTypeID, desc = SqlUdf.SubCodeName(s.SkillTypeID, langId) }, YearsOf_Experience = s.YearsOf_Experience, IsOtherSkill = false }).ToList(), #endregion #region Training Info Trainings = user.IndividualDetail.IndividualTrainingDetails.Where(w => w.IsDeleted == null) .Select(t => new IndividualProfileTrainingVM() { IndividualTrainingID = (long)t.IndividualTrainingID, TrainingStartDate = t.TrainingStartDate, TrainingEndDate = t.TrainingEndDate, TrainingName = t.IndividualTrainingDetails_Det.FirstOrDefault( l => l.LanguageID == langId && l.IndividualTrainingID == t.IndividualTrainingID) .TrainingName, TrainingProviderName = t.IndividualTrainingDetails_Det.FirstOrDefault(l => l.LanguageID == langId) .TrainingProviderName }).ToList(), #endregion #region Certificates Info Certificates = user.IndividualDetail.IndividualCertificationDetails.Where(w => w.IsDeleted == null) .Select(c => new IndividualProfileCertificateVM() { IndividualCertificationID = (long)c.IndividualCertificationID, CertificateName = c.IndividualCertificationDetails_Det.FirstOrDefault( l => l.LanguageID == langId && l.IndividualCertificationID == c.IndividualCertificationID).CertificateName, CertificateIssueDate = c.CertificateIssueDate, CertificateValidUntil = c.CertificateValidUntil }).ToList(), #endregion }).Single(); #region load localized values indObj.FirstName = portalObj.First() .IndividualDetail.IndividualDetailsDets.Select(d => new LocalString(d.LanguageID, d.FirstName)) .ToList(); indObj.LastName = portalObj.First() .IndividualDetail.IndividualDetailsDets.Select(d => new LocalString(d.LanguageID, d.LastName)) .ToList(); indObj.Address = portalObj.First() .IndividualDetail.IndividualDetailsDets.Select(d => new LocalString(d.LanguageID, d.Address)) .ToList(); #endregion #region Other skills indObj.Skills = indObj.Skills.Concat( db.IndividualOtherSkills.Where(pu => pu.PortalUsersID == portalUserId) .Select(o => new IndividualProfileSkillVM() { Skill = new CodeSet { id = null, desc = o.OtherSkill }, Industry = new CodeSet { id = o.IndustryId, desc = SqlUdf.SubCodeName(o.IndustryId, langId) }, SkillLevel = new CodeSet { id = o.SkillLevelId, desc = SqlUdf.SubCodeName(o.SkillLevelId, langId) }, SkillType = new CodeSet { id = null, desc = null }, YearsOf_Experience = "-", IsOtherSkill = true })).ToList(); #endregion #region Applied Jobs indObj.Jobs = db.JobApplieds.Where(j => j.IndPortalUserID == portalUserId).Select( job => new IndividualProfileAppliedJobsVM() { OrganizationName = job.JobOffer.OrganizationDetail.OrganizationDetails_Det.FirstOrDefault( l => l.LanguageID == langId && l.PortalUsersID == job.JobOffer.PortalUsersID) .OrganizationName, ApplyDate = job.ApplyDate, Title = SqlUdf.SubCodeName(job.JobOffer.JobTiltleID, langId), JobDescription = job.JobOffer.JobOfferDetails.FirstOrDefault( l => l.LanguageID == langId && l.JobOfferID == job.JobOfferID).JobDescription, ViewStatus = job.ViewStatus, ExpiryDate = job.JobOffer.EndDate, JobAppliedID = job.JobAppliedID, JobOfferID = job.JobOfferID }).OrderByDescending(o => o.ApplyDate).ToList(); #endregion #region Applied Trainings indObj.AppliedTrainings = db.TrainingApplies.Where(j => j.IndPortalUserID == portalUserId).Select( training => new IndividualProfileAppliedTrainingVM() { OrganizationName = training.TrainingOffer.OrganizationDetail.OrganizationDetails_Det.FirstOrDefault( l => l.LanguageID == langId && l.PortalUsersID == training.TrainingOffer.PortalUsersID) .OrganizationName, ApplyDate = training.ApplyDate, CourseName = SqlUdf.SubCodeName(training.TrainingOffer.CourseNameID, langId), TrainingDescription = training.TrainingOffer.TrainingOfferDetails.FirstOrDefault( l => l.LanguageID == langId && l.TrainingOfferID == training.TrainingAppliedID) .CourseDescription, ViewStatus = training.ViewStatus, ExpiryDate = training.TrainingOffer.EndDate, TrainingAppliedID = training.TrainingAppliedID, TrainingOfferID = training.TrainingOfferID }).OrderByDescending(o => o.ApplyDate).ToList(); #endregion indObj.Reviews = new Dictionary <string, object>() { { "FirstName", new GlobalString( portalObj.First() .IndividualDetail.IndividualDetailsDets.Select( x => new LocalString(x.LanguageID, x.FirstName)) .ToList(), "") }, { "LastName", new GlobalString( portalObj.First() .IndividualDetail.IndividualDetailsDets.Select( x => new LocalString(x.LanguageID, x.LastName)) .ToList(), "") }, { "Address", new GlobalString( portalObj.First() .IndividualDetail.IndividualDetailsDets.Select( x => new LocalString(x.LanguageID, x.Address)) .ToList(), "") }, { "Certificates", portalObj.First() .IndividualDetail.IndividualCertificationDetails.Where(x => x.IsDeleted == null) .Select( x => new GlobalString( x.IndividualCertificationDetails_Det.Select( y => new LocalString(y.LanguageID, y.CertificateName)).ToList(), "")) .ToList() }, { "EduInstNames", portalObj.First() .IndividualDetail.IndividualEducationlevels.Where(x => x.IsDeleted == null) .Select( x => new GlobalString( x.IndividualEducationlevelDets.Select( y => new LocalString(y.LanguageID, y.InstitutionName)).ToList(), "")) .ToList() }, { "EduInstTypes", portalObj.First() .IndividualDetail.IndividualEducationlevels.Where(x => x.IsDeleted == null) .Select( x => new GlobalString( x.IndividualEducationlevelDets.Select( y => new LocalString(y.LanguageID, y.InstitutionType)).ToList(), "")) .ToList() }, { "EduCertTypes", portalObj.First() .IndividualDetail.IndividualEducationlevels.Where(x => x.IsDeleted == null) .Select( x => new GlobalString( x.IndividualEducationlevelDets.Select( y => new LocalString(y.LanguageID, y.CertificationType)).ToList(), "")) .ToList() }, { "EduFaculties", portalObj.First() .IndividualDetail.IndividualEducationlevels.Where(x => x.IsDeleted == null) .Select( x => new GlobalString( x.IndividualEducationlevelDets.Select( y => new LocalString(y.LanguageID, y.FacultyName)).ToList(), "")) .ToList() }, { "EduGrades", portalObj.First() .IndividualDetail.IndividualEducationlevels.Where(x => x.IsDeleted == null) .Select( x => new GlobalString( x.IndividualEducationlevelDets.Select( y => new LocalString(y.LanguageID, y.Grade)).ToList(), "")) .ToList() }, { "ExpEmployers", portalObj.First() .IndividualDetail.IndividualExperienceDetails.Where(x => x.IsDeleted == null) .Select( x => new GlobalString( x.IndividualExperienceDetails_Det.Select( y => new LocalString(y.LanguageID, y.EmployerName)).ToList(), "")) .ToList() }, { "ExpJobs", portalObj.First() .IndividualDetail.IndividualExperienceDetails.Where(x => x.IsDeleted == null) .Select( x => new GlobalString( x.IndividualExperienceDetails_Det.Select( y => new LocalString(y.LanguageID, y.EmploymentJobDescription)).ToList(), "")) .ToList() }, { "TrProviders", portalObj.First() .IndividualDetail.IndividualTrainingDetails.Where(x => x.IsDeleted == null) .Select( x => new GlobalString( x.IndividualTrainingDetails_Det.Select( y => new LocalString(y.LanguageID, y.TrainingProviderName)).ToList(), "")) .ToList() }, { "TrNames", portalObj.First() .IndividualDetail.IndividualTrainingDetails.Where(x => x.IsDeleted == null) .Select( x => new GlobalString( x.IndividualTrainingDetails_Det.Select( y => new LocalString(y.LanguageID, y.TrainingName)).ToList(), "")) .ToList() }, }; } return(indObj); }