public static List <JobCodeMapper> QueryJobCodeMapper() { string filePath = System.AppDomain.CurrentDomain.BaseDirectory.ToString() + "对照表(广元人才网) 备份.xlsx"; if (filePath != "") { if (filePath.Contains("xls"))//判断文件是否存在 { int k = 0; string conn = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" + filePath + ";Extended Properties=Excel 8.0;"; OleDbConnection oleCon = new OleDbConnection(conn); oleCon.Open(); string Sql = "select * from [工种对应$]"; OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, oleCon); DataSet ds = new DataSet(); mycommand.Fill(ds, "[工种对应$]"); oleCon.Close(); int count = ds.Tables["[工种对应$]"].Rows.Count; List <JobCodeMapper> codeMappers = new List <JobCodeMapper>(); for (int i = 0; i < count; i++) { JobCodeMapper model = new JobCodeMapper(); string yy = ds.Tables["[工种对应$]"].Rows[i][0].ToString().Trim(); string xx = ds.Tables["[工种对应$]"].Rows[i][1].ToString().Trim(); model.codeValue = ds.Tables["[工种对应$]"].Rows[i][0].ToString().Trim(); model.localcodeValue = ds.Tables["[工种对应$]"].Rows[i][4].ToString().Trim(); codeMappers.Add(model); } return(codeMappers); } else { return(null); } } else { return(null); } }
public PersonnelWithContractDetails Details(int id, string deductionCode, int?fromYear, int?toYear) { var detailsRecord = (from employee in _context.AcctEmployee join personnel in _context.AcctPersonnel on employee.Ssnumber equals personnel.Ssnumber join jobCodeRecord in _context.AcctPayrollJobCodes on employee.JobCode equals jobCodeRecord.JobCode into employeeJobCode from jobCode in employeeJobCode.DefaultIfEmpty() where employee.Id == id select new { Peronnel = PersonnelMapper.Map(personnel), Contract = EmployeeMapper.Map(employee), JobCode = (jobCode == null) ? null : JobCodeMapper.Map(jobCode) } ).SingleOrDefault(); if (detailsRecord != null) { var deductions = _context.AcctEmployeeMiscDeductions.Where(t => t.EmployeeNumber == detailsRecord.Contract.PersonNumber && (string.IsNullOrWhiteSpace(deductionCode) || t.MiscCode == deductionCode.Trim()) && (fromYear == null || t.MiscYear.Value >= fromYear.Value) && (toYear == null || t.MiscYear.Value <= toYear.Value)) .OrderBy(t => t.MiscYear) .Select(DeductionMapper.Map) .ToList(); var ytdHistories = _context.AcctEmployeeYtdhistory.Where(t => t.Ssnumber == detailsRecord.Contract.SSNumber && (fromYear == null || t.Year.Value >= fromYear.Value) && (toYear == null || t.Year.Value <= toYear.Value)) .OrderBy(t => t.Year) .Select(YtdHistoryMapper.Map) .ToList(); return(EmployeeMapper.Map(detailsRecord.Peronnel, detailsRecord.Contract, detailsRecord.JobCode, deductions, ytdHistories)); } else { return(null); } }
public List <PersonResume> QueryPersonResume() { string comText = @"SELECT pr.Number,left(pbi.AccountID,18)AccountID, CONVERT(varchar(100), pr.UpdateTime, 20)UpdateTime , pr.ResumeName , OpenStatus =case when pr.OpenStatus=0 then 0 else 1 end, Intention = CASE WHEN jwi.Intention = NULL THEN 1 ELSE 0 END , Experiences = CASE WHEN pwe.Experiences = NULL THEN 1 ELSE 0 END , EducationAndTrainingBackground = CASE WHEN et.EducationAndTrainingBackground = NULL THEN 1 ELSE 0 END , SUBSTRING(pr.SelfEvaluation, 0, 500) SelfEvaluation , cj.JobID , cj.JobName, WorkingMode=CASE WHEN jwi.WorkingMode=2 THEN '1' WHEN jwi.WorkingMode=1 THEN '2' ELSE '3' END, Years = case when pwe.Years<1 and pwe.Years>0 then 1 when pwe.Years<3 then 2 when pwe.Years>=3 and pwe.Years<=5 then 3 when pwe.Years>5 and pwe.Years<=10 then 4 when pwe.Years>10 then 5 else 0 end FROM dbo.PersonResume pr JOIN dbo.PersonBaseInfo pbi ON pr.InfoID=pbi.Id JOIN dbo.PersonJobWantedIntention jwi ON jwi.ResumeID = pr.Id JOIN PersonWorkExperience pwe ON pwe.ResumeID = pr.Id JOIN dbo.PersonEducationAndTraining et ON et.ResumeID = pr.Id JOIN (select *from( select *,ROW_NUMBER() over(partition by p2.JobId order by p2.JobId)sequence from CompanyJob p2 )t where t.sequence=1) cj ON CONVERT(VARCHAR(50), cj.JobID) = (SELECT top 1 * FROM dbo.StringSplit(jwi.ExpectedJobID ,',')) WHERE pr.IsAudited = 1"; DataTable resumeInfoTable = SqlHelper.ExecuteDataTable(comText, new SqlParameter("@param", DBNull.Value)); List <JobCodeMapper> codeMappers = SqlHelper.QueryJobCodeMapper(); string cmdText2 = "select * from PullInfoRecord where type='个人简历'"; DataTable yetresumeInfoTable = SqlHelper.ExecuteDataTable(cmdText2, new SqlParameter("@param", DBNull.Value)); List <YetInsertInfo> YetInsertInfolist = new List <YetInsertInfo>(); List <PersonResume> resumeInfolist = new List <PersonResume>(); if (yetresumeInfoTable.Rows.Count > 0) { foreach (DataRow item in yetresumeInfoTable.Rows) { YetInsertInfo yetInsertInfo = new YetInsertInfo() { number = item["number"].ToString(), type = item["type"].ToString(), updateTime = item["updateTime"].ToString() }; YetInsertInfolist.Add(yetInsertInfo); } } if (resumeInfoTable.Rows.Count > 0) { foreach (DataRow item in resumeInfoTable.Rows) { PersonResume PersonResume = new PersonResume() { acc200 = item["Number"].ToString(), aac001 = item["AccountID"].ToString(), acc201 = item["ResumeName"].ToString(), acc202 = item["OpenStatus"].ToString(), acc205 = item["Intention"].ToString(), acc206 = item["Experiences"].ToString(), acc208 = item["EducationAndTrainingBackground"].ToString(), //acc209 = item["SelfEvaluation"].ToString(), //aca111 = aca112 = item["JobName"].ToString(), acc217 = item["Years"].ToString(), //必填项 //是否默认简历(0:默认,1:非默认) acc203 = "0", //登记日期 aae043 = item["UpdateTime"].ToString(), //更新时间 aae044 = item["UpdateTime"].ToString(), yae100 = "广元人才中心", //登记时间 aae036 = item["UpdateTime"].ToString(), aae017 = "广元人才中心", aae011 = "广元市", aae022 = "510800000000", ycb213 = item["WorkingMode"].ToString(), acb215 = "510800000000" }; //PersonResume.aca111 JobCodeMapper cm = codeMappers.Where(c => !string.IsNullOrWhiteSpace(c.ID) && c.ID.Equals(item["JobID"])).FirstOrDefault(); if (cm == null) { PersonResume.aca111 = "4010000"; } else { PersonResume.aca111 = cm.codeValue; } resumeInfolist.Add(PersonResume); } } DateTimeFormatInfo dtFormat = new DateTimeFormatInfo(); dtFormat.ShortDatePattern = "yyyy/MM/dd"; //需要推送的信息 过滤:未插入,插入但更新时间大于推送时间 List <PersonResume> personResumes1 = resumeInfolist.Where(r => !YetInsertInfolist.Any(y => y.number == r.acc200)).ToList(); List <PersonResume> personResumes2 = resumeInfolist.Where(r => YetInsertInfolist.Any(y => y.number == r.acc200 && Convert.ToDateTime(y.updateTime, dtFormat) < Convert.ToDateTime(r.aae044, dtFormat))).ToList(); List <PersonResume> personResumes = personResumes1.Union(personResumes2).ToList <PersonResume>(); return(personResumes); }
public List <PersonResume> QueryPersonResume() { string comText = @"SELECT * FROM ( SELECT pr.Number , ROW_NUMBER() OVER ( PARTITION BY Number ORDER BY Number ) sequence , LEFT(pbi.AccountID, 18) AccountID , CONVERT(VARCHAR(100), pr.UpdateTime, 20) UpdateTime , pr.ResumeName , OpenStatus = CASE WHEN pr.OpenStatus = 0 THEN 0 ELSE 1 END , Intention = CASE WHEN jwi.Intention = NULL THEN 1 ELSE 0 END , Experiences = CASE WHEN pwe.Experiences = NULL THEN 1 ELSE 0 END , EducationAndTrainingBackground = CASE WHEN et.EducationAndTrainingBackground = NULL THEN 1 ELSE 0 END , SUBSTRING(pr.SelfEvaluation, 0, 500) SelfEvaluation , cj.JobID , cj.JobName , WorkingMode = CASE WHEN jwi.WorkingMode = 2 THEN '1' WHEN jwi.WorkingMode = 1 THEN '2' ELSE '3' END , Years = CASE WHEN pwe.Years < 1 AND pwe.Years > 0 THEN 1 WHEN pwe.Years < 3 THEN 2 WHEN pwe.Years >= 3 AND pwe.Years <= 5 THEN 3 WHEN pwe.Years > 5 AND pwe.Years <= 10 THEN 4 WHEN pwe.Years > 10 THEN 5 ELSE 0 END FROM dbo.PersonResumes pr JOIN dbo.PersonBaseInfo pbi ON pr.InfoID = pbi.Id JOIN dbo.PersonJobWantedIntention jwi ON jwi.ResumeID = pr.Id JOIN PersonWorkExperience pwe ON pwe.ResumeID = pr.Id JOIN dbo.PersonEducationAndTraining et ON et.ResumeID = pr.Id JOIN ( SELECT * FROM ( SELECT * , ROW_NUMBER() OVER ( PARTITION BY p2.JobID ORDER BY p2.JobId ) sequence FROM RecruitmentJobs p2 ) t WHERE t.sequence = 1 ) cj ON CONVERT(VARCHAR(50), cj.JobID) = ( SELECT TOP 1 * FROM dbo.StringSplit(jwi.ExpectedJobID, ',') ) WHERE pr.IsAudited = 1 ) t WHERE t.sequence = 1"; DataTable resumeInfoTable = SqlHelper.ExecuteDataTable(comText, new SqlParameter("@param", DBNull.Value)); List <JobCodeMapper> codeMappers = SqlHelper.QueryJobCodeMapper(); string cmdText2 = "select * from PullInfoRecord where type='个人简历'"; DataTable yetresumeInfoTable = SqlHelper.ExecuteDataTable(cmdText2, new SqlParameter("@param", DBNull.Value)); List <YetInsertInfo> YetInsertInfolist = new List <YetInsertInfo>(); List <PersonResume> resumeInfolist = new List <PersonResume>(); if (yetresumeInfoTable.Rows.Count > 0) { foreach (DataRow item in yetresumeInfoTable.Rows) { YetInsertInfo yetInsertInfo = new YetInsertInfo() { number = item["number"].ToString(), type = item["type"].ToString(), updateTime = item["updateTime"].ToString() }; YetInsertInfolist.Add(yetInsertInfo); } } if (resumeInfoTable.Rows.Count > 0) { foreach (DataRow item in resumeInfoTable.Rows) { PersonResume PersonResume = new PersonResume() { acc200 = item["Number"].ToString(), aac001 = item["AccountID"].ToString(), acc201 = item["ResumeName"].ToString(), acc202 = item["OpenStatus"].ToString(), acc205 = item["Intention"].ToString(), acc206 = item["Experiences"].ToString(), acc208 = item["EducationAndTrainingBackground"].ToString(), //acc209 = item["SelfEvaluation"].ToString(), //aca111 = aca112 = item["JobName"].ToString(), acc217 = item["Years"].ToString(), //必填项 //必填项 //是否默认简历(0:默认,1:非默认) acc203 = "0", // 登记日期 aae043 = item["UpdateTime"].ToString(), yae100 = "攀枝花市人才服务中心", //登记时间 aae036 = item["UpdateTime"].ToString(), aae017 = "攀枝花市人才服务中心", //经办机构 aae011 = "攀枝花市", //登记地区行政区划代码 aae022 = "510401000000", ycb213 = item["WorkingMode"].ToString(), //工作地点代码 acb215 = "510401000000" }; //PersonResume.aca111 JobCodeMapper cm = codeMappers.Where(c => !string.IsNullOrWhiteSpace(c.ID) && c.ID.Equals(item["JobID"])).FirstOrDefault(); if (cm == null) { PersonResume.aca111 = "4010000"; } else { PersonResume.aca111 = cm.typeCode; } resumeInfolist.Add(PersonResume); } } DateTimeFormatInfo dtFormat = new DateTimeFormatInfo(); dtFormat.ShortDatePattern = "yyyy/MM/dd"; //需要推送的信息 过滤:未插入,插入但更新时间大于推送时间 List <PersonResume> personResumes1 = resumeInfolist.Where(r => !YetInsertInfolist.Any(y => y.number == r.acc200)).ToList(); //List<PersonResume> personResumes2 = resumeInfolist.Where(r => YetInsertInfolist.Any(y => y.number == r.acc200 && Convert.ToDateTime(y.updateTime, dtFormat) //< Convert.ToDateTime(r.aae043, dtFormat))).ToList(); //List<PersonResume> personResumes = personResumes1.Union(personResumes2).ToList<PersonResume>(); return(personResumes1); }