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);
}
