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 <CompanyJob> QueryCompanyJobInfo()
{
string cmdText = @"SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY j.JobNumber ORDER BY JobNumber ) AS sequece ,
j.JobNumber ,
LEFT(cbi.Id, 18) cid ,
j.JobName ,
LEFT(j.[Description], 300) require ,
LEFT(j.[Description], 300) Descrip ,
j.Amount ,
LEFT(j.[Address], 30) [Address] ,
WorkingMode = CASE WHEN j.WorkingMode = 2 THEN '1'
WHEN j.WorkingMode = 1 THEN '2'
ELSE '3'
END ,
j.BeginAge ,
j.EndAge ,
WorkYears = CASE WHEN j.WorkYears < 1
AND j.WorkYears > 0 THEN 1
WHEN j.WorkYears < 3 THEN 2
WHEN j.WorkYears >= 3
AND j.WorkYears <= 5 THEN 3
WHEN j.WorkYears > 5
AND j.WorkYears <= 10 THEN 4
WHEN j.WorkYears > 10 THEN 5
ELSE 0
END ,
cbi.ContactOne ,
cbi.ContactOneMobile ,
cbi.ContactOnePhone ,
CONVERT(VARCHAR(100), j.CreateTime, 20) CreateTime ,
CONVERT(VARCHAR(100), j.UpdateTime, 20) UpdateTime ,
CONVERT(VARCHAR(100), j.PublishDate, 20) PublishDate ,
CONVERT(VARCHAR(100), j.EndTime, 20) EndTime ,
j.[Status] ,
ipc.ID [sid] ,
ipc.ParentID spid ,
ipc.ItemName ,
ca.AgentName ,
j.JobID
FROM dbo.CompanyJobs j
JOIN dbo.CompanyBaseInfo cbi ON cbi.Id = j.CompanyID
JOIN ( SELECT ca.CompanyID ,
ca.AgentName
FROM CompanyAgents ca
GROUP BY ca.CompanyID ,
ca.AgentName
) ca ON ca.CompanyID = cbi.Id
JOIN dbo.ItemProfessionalCategory ipc ON ipc.ID = j.ProfessionalCategoryID
WHERE j.[Status] = 2
AND cbi.ContactOne IS NOT NULL
) aaa
WHERE aaa.sequece = 1;";
DataTable companyjobInfoTable = PullToScxtpt_px.Helper.SqlHelper.ExecuteDataTable(cmdText, new SqlParameter("@IsAudit", 1));
List <JobCodeMapper> codeMappers = SqlHelper.QueryJobCodeMapper();
List <SpecialityCodeMapper> SpecialitycodeMappers = SqlHelper.QuerySpecialityCodeMapper();
string cmdText2 = "select * from PullInfoRecord where type='招聘信息'";
DataTable yetcompanyjobInfoTable = SqlHelper.ExecuteDataTable(cmdText2, new SqlParameter("@param", DBNull.Value));
List <YetInsertInfo> YetInsertInfolist = new List <YetInsertInfo>();
List <CompanyJob> companyjobInfolist = new List <CompanyJob>();
if (yetcompanyjobInfoTable.Rows.Count > 0)
{
foreach (DataRow item in yetcompanyjobInfoTable.Rows)
{
YetInsertInfo yetInsertInfo = new YetInsertInfo()
{
number = item["number"].ToString(),
type = item["type"].ToString(),
updateTime = item["updateTime"].ToString()
};
YetInsertInfolist.Add(yetInsertInfo);
}
}
if (companyjobInfoTable.Rows.Count > 0)
{
foreach (DataRow item in companyjobInfoTable.Rows)
{
CompanyJob companyJob = new CompanyJob();
//招聘编号
companyJob.acb200 = item["JobNumber"].ToString();
//单位编号
companyJob.aab001 = item["cid"].ToString();
//岗位名称
companyJob.aca112 = item["JobName"].ToString();
//岗位描述
companyJob.acb22a = item["Descrip"].ToString();
//岗位要求
var aca111 = codeMappers.Where(c => item["JobID"].ToString().ToUpper()
.Equals(c.ID)).FirstOrDefault();
//try
//{
// companyJob.aca111 = aca111.typeCode.ToString();
//}
//catch (Exception)
//{
// System.Diagnostics.Debug.WriteLine(item["JobID"].ToString() + "----" + item["JobName"].ToString());
//}
if (aca111 == null || string.IsNullOrWhiteSpace(aca111.typeCode.ToString()))
{
continue;
}
else
{
companyJob.aca111 = aca111.typeCode.ToString();
}
//所学专业
var aac183 = SpecialitycodeMappers.Where(c => item["sid"].ToString().ToUpper()
.Equals(c.ID)).FirstOrDefault();
if (aac183 == null)
{
var _aac183 = SpecialitycodeMappers.Where(c => item["spid"].ToString().ToUpper()
.Equals(c.ID)).FirstOrDefault();
if (_aac183 == null)
{
continue;
}
else
{
companyJob.aac183 = _aac183.typeCode.ToString();
}
//try
//{
// companyJob.aac183 = _aac183.typeCode.ToString();
//}
//catch (Exception)
//{
// System.Diagnostics.Debug.WriteLine(item["sid"].ToString() + " "+ item["spid"].ToString() + " " + item["ItemName"].ToString());
//}
}
else
{
companyJob.aac183 = aac183.typeCode.ToString();
}
//招聘人数
companyJob.acb240 = item["Amount"].ToString();
//工作地点
companyJob.acb202 = item["Address"].ToString();
//工作地点行政区划代码
companyJob.aab301 = "510400000000";
//工作性质
companyJob.acb239 = item["WorkingMode"].ToString();
//最低年龄
companyJob.acb221 = item["BeginAge"].ToString();
//最高年龄
companyJob.acb222 = item["EndAge"].ToString();
//从业年数
companyJob.acc217 = item["WorkYears"].ToString();
//联系人
companyJob.aae004 = item["ContactOne"].ToString();
if (string.IsNullOrWhiteSpace(companyJob.aae004))
{
continue;
}
//联系电话
companyJob.aae005 = item["ContactOneMobile"].ToString();
if (string.IsNullOrWhiteSpace(companyJob.aae005))
{
if (!string.IsNullOrWhiteSpace(item["ContactOnePhone"].ToString()))
{
companyJob.aae005 = item["ContactOnePhone"].ToString();
}
else
{
continue;
}
}
//创建日期
companyJob.aae395 = item["CreateTime"].ToString();
//更新日期
companyJob.aae396 = item["UpdateTime"].ToString();
//发布日期
companyJob.aae397 = item["PublishDate"].ToString();
//到期日期
companyJob.aae398 = item["EndTime"].ToString();
//审核状态
companyJob.ace751 = "1";
//发布状态
companyJob.ace752 = item["Status"].ToString();
//数据来源
//登记时间
companyJob.aae036 = item["PublishDate"].ToString();
//登记机构
companyJob.aae017 = "攀枝花市人才服务中心";
//登记人
companyJob.aae011 = "市场部";
//登记地行政区划代码
companyJob.aae022 = "510400000000";
companyjobInfolist.Add(companyJob);
}
}
DateTimeFormatInfo dtFormat = new DateTimeFormatInfo();
dtFormat.ShortDatePattern = "yyyy/MM/dd";
//需要推送的信息 过滤：未插入，插入但更新时间大于推送时间
List <CompanyJob> companyJobs1 = companyjobInfolist.Where(r => !YetInsertInfolist.Any(y => y.number == r.acb200)).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(companyJobs1);
}
public List <CompanyInfo> QueryCompanyInfo()
{
string comText = @"SELECT * FROM (SELECT ROW_NUMBER() OVER(PARTITION BY cb.ID ORDER BY cb.ID)AS num,
LEFT(cb.ID,18)ID,
cb.FullName,
icn.ID icnItemCode,
ics.ID icsItemCode,
ict.ID ictItemCode,
cb.SiteUrl,
cb.Postalcode,
substring(cb.[Address],0,10)cbAddress,
cb.ContactOne,
cb.ContactOneMobile,
cb.ContactOnePhone,
cb.ContactOneEmail,
cb.Introduction,
cl.PersonName,
cl.IssuingOrgan,
cl.RegisteredCapital,
CONVERT(varchar(100), cl.SetUpDate, 20)SetUpDate ,
CONVERT(varchar(100), cl.InspectionDate, 20)InspectionDate ,
cl.[Address] clAddress,
ca.AgentName
FROM CompanyBaseInfo cb join CompanyLicence cl on cb.Id = cl.CompanyID
JOIN ItemCompanyNature icn
ON icn.ID = cb.NatureID join ItemCompanyScale ics
ON ics.ID = cb.ScaleID
JOIN ItemCompanyTrade ict
ON ict.ID = cb.TradeID
JOIN (SELECT ca.CompanyID, ca.AgentName FROM CompanyAgents ca GROUP BY ca.CompanyID,ca.AgentName) ca ON ca.CompanyID=cb.Id
where cb.IsAudit = @IsAudit )aaa WHERE aaa.num=1";
DataTable companyInfoTable = SqlHelper.ExecuteDataTable(comText, new SqlParameter("@IsAudit", 1));
List <CodeMapper> codeMappers = SqlHelper.QueryCodeMapper();
string cmdText2 = "select * from PullInfoRecord where type='企业信息'";
DataTable yetcompanyInfoTable = SqlHelper.ExecuteDataTable(cmdText2, new SqlParameter("@param", DBNull.Value));
List <YetInsertInfo> YetInsertInfolist = new List <YetInsertInfo>();
List <CompanyInfo> companyInfolist = new List <CompanyInfo>();
if (yetcompanyInfoTable.Rows.Count > 0)
{
foreach (DataRow item in yetcompanyInfoTable.Rows)
{
YetInsertInfo yetInsertInfo = new YetInsertInfo()
{
number = item["number"].ToString(),
type = item["type"].ToString(),
updateTime = item["updateTime"].ToString()
};
YetInsertInfolist.Add(yetInsertInfo);
}
}
if (companyInfoTable.Rows.Count > 0)
{
foreach (DataRow item in companyInfoTable.Rows)
{
CompanyInfo companyInfo = new CompanyInfo()
{
aab001 = item["ID"].ToString(),
aab004 = item["FullName"].ToString(),
// aae392 = item["SiteUrl"].ToString(),
// aae007 = item["Postalcode"].ToString(),
acb501 = item["ContactOneMobile"].ToString(),
aae005 = item["ContactOnePhone"].ToString(),
// aae159 = item["ContactOneEmail"].ToString(),
aab013 = item["PersonName"].ToString(),
aab049 = item["RegisteredCapital"].ToString(),
aae396 = item["InspectionDate"].ToString(),
aae022 = "510400000000",
//aab998 = item["Nsrsbm"].ToString(),
aab301 = "510400000000",
aae011 = "攀枝花市",
// yae100 = "攀枝花市人才中心"
};
//单位类型
var aab019 = codeMappers.Where(c => item["icnItemCode"].ToString().ToUpper().
Equals(c.localCodeValue)).Where(c => c.codeType.Equals("AAB019")).FirstOrDefault();
if (aab019 == null)
{
companyInfo.aab019 = "10";
}
else
{
companyInfo.aab019 = aab019.codeValue.ToString();
}
// 所属行业
var aab022 = codeMappers.Where(c => item["ictItemCode"].ToString().ToUpper().
Equals(c.localCodeValue)).FirstOrDefault();
if (aab022 == null)
{
companyInfo.aab022 = "0800";
}
else
{
companyInfo.aab022 = aab022.codeValue.ToString();
}
// 经济类型
var aab020 = codeMappers.Where(c => item["icnItemCode"].ToString().ToUpper().
Equals(c.localCodeValue)).Where(c => c.codeType.Equals("AAB020")).FirstOrDefault();
if (aab020 == null)
{
companyInfo.aab020 = "150";
}
else
{
companyInfo.aab020 = aab020.codeValue.ToString();
}
//登记时间
var aae036 = item["SetUpDate"].ToString();
if (string.IsNullOrEmpty(aae036))
{
companyInfo.aae036 = DateTime.Now.ToLocalTime().ToString();
}
else
{
companyInfo.aae036 = aae036;
}
//人员规模
if (item["icsItemCode"].ToString() == "C" || item["icsItemCode"].ToString() == "D" ||
item["icsItemCode"].ToString() == "E")
{
companyInfo.aab056 = "5";
}
else if (item["icsItemCode"].ToString() == "F")
{
companyInfo.aab056 = "4";
}
else if (true)
{
companyInfo.aab056 = "6";
}
//登记机构
companyInfo.aae017 = item["IssuingOrgan"].ToString();
if (string.IsNullOrEmpty(companyInfo.aae017))
{
companyInfo.aae017 = "攀枝花市人才服务中心";
}
else
{
companyInfo.aae017 = item["IssuingOrgan"].ToString();
}
//联系人
companyInfo.aae004 = item["ContactOne"].ToString();
if (string.IsNullOrEmpty(companyInfo.aae004))
{
companyInfo.aae004 = item["AgentName"].ToString();
}
else
{
companyInfo.aae004 = item["ContactOne"].ToString();
}
//联系人地址
companyInfo.aae006 = item["cbAddress"].ToString();
if (string.IsNullOrEmpty(companyInfo.aae006))
{
companyInfo.aae006 = "攀枝花市";
}
else
{
companyInfo.aae006 = item["cbAddress"].ToString();
}
companyInfolist.Add(companyInfo);
}
}
DateTimeFormatInfo dtFormat = new DateTimeFormatInfo();
dtFormat.ShortDatePattern = "yyyy/MM/dd";
//需要推送的信息 过滤：未插入，插入但更新时间大于推送时间
List <CompanyInfo> companyInfos1 = companyInfolist.Where(r => !YetInsertInfolist.Any(y => y.number == r.aab001)).ToList();
//List<CompanyInfo> companyInfos2 = companyInfolist.Where(r => YetInsertInfolist.Any(y => y.number == r.aab001 && Convert.ToDateTime(y.updateTime, dtFormat)
//< Convert.ToDateTime(r.aae396, dtFormat))).ToList();
//List<CompanyInfo> companyInfos = companyInfos1.Union(companyInfos2).ToList<CompanyInfo>();
return(companyInfos1);
}
public List <PersonInfo> QueryPersonInfo()
{
string cmdText = @"SELECT LEFT(p.AccountID, 18) AccountID ,
p.PersonName ,
p.IDCardNo ,
Sex = CASE WHEN p.Sex = '男' THEN 1
ELSE 2
END ,
nat.ID natItemCode ,
CONVERT(VARCHAR(100), p.Birthday, 20) Birthday ,
MaritalStatus = CASE WHEN p.MaritalStatus = 0
THEN 1
WHEN p.MaritalStatus = 1
THEN 2
ELSE 4
END ,
ps.ID psItemCode ,
Years = CASE WHEN we.Years < 1
AND we.Years > 0
THEN 1
WHEN we.Years < 3 THEN 2
WHEN we.Years >= 3
AND we.Years <= 5
THEN 3
WHEN we.Years > 5
AND we.Years <= 10
THEN 4
WHEN we.Years > 10 THEN 5
ELSE 0
END ,
deg.ID degItemCode ,
eat.GraduateSchool ,
eat.Major ,
CONVERT(VARCHAR(100), eat.GraduationTime, 20) GraduationTime ,
p.Mobile ,
p.Height ,
ic.Addr
FROM ( SELECT pbi.Id pid ,
pbi.AccountID ,
pbi.PersonName ,
pbi.Sex ,
pbi.IDCardNo ,
pbi.Birthday ,
pbi.NationID ,
pbi.PermanentResidenceID ,
pbi.PoliticalStatusID ,
pbi.MaritalStatus ,
pbi.Height ,
pbi.Mobile ,
res.Id rid ,
res.InfoID ,
res.IsAudited
FROM PersonBaseInfo pbi
JOIN dbo.PersonResumes res ON res.InfoID = pbi.Id
WHERE res.IsAudited = 1 AND IDCardNo !='' AND IDCardNo IS NOT NULL
) p
LEFT JOIN ItemNation nat ON nat.ID = p.NationID
LEFT JOIN ItemPoliticalStatus ps ON ps.ID = p.PoliticalStatusID
LEFT JOIN dbo.PersonWorkExperience we ON we.ResumeID = rid
LEFT JOIN dbo.PersonEducationAndTraining eat ON eat.ResumeID = rid
LEFT JOIN dbo.ItemDegree deg ON eat.HighestDegreeID = deg.ID
LEFT JOIN ( SELECT c1.ID ,
c2.ItemName
+ c1.ItemName Addr
FROM dbo.ItemCities c1 ,
dbo.ItemCities c2
WHERE c1.ParentID = CONVERT(VARCHAR(50), c2.ID)
) ic ON ic.ID = p.PermanentResidenceID";
DataTable personTable = SqlHelper.ExecuteDataTable(cmdText, new SqlParameter("@param", DBNull.Value));
List <CodeMapper> codeMappers = SqlHelper.QueryCodeMapper();
string cmdText2 = "select * from PullInfoRecord where type='个人信息'";
DataTable yetpersonInfoTable = SqlHelper.ExecuteDataTable(cmdText2, new SqlParameter("@param", DBNull.Value));
List <YetInsertInfo> YetInsertInfolist = new List <YetInsertInfo>();
List <PersonInfo> personInfolist = new List <PersonInfo>();
if (yetpersonInfoTable.Rows.Count > 0)
{
foreach (DataRow item in yetpersonInfoTable.Rows)
{
YetInsertInfo yetInsertInfo = new YetInsertInfo()
{
number = item["number"].ToString(),
type = item["type"].ToString(),
updateTime = item["updateTime"].ToString()
};
YetInsertInfolist.Add(yetInsertInfo);
}
}
if (personTable.Rows.Count > 0)
{
foreach (DataRow item in personTable.Rows)
{
PersonInfo personInfo = new PersonInfo()
{
aac001 = item["AccountID"].ToString(),
aac003 = item["PersonName"].ToString(),
aac004 = item["Sex"].ToString(),
aac006 = item["Birthday"].ToString(),
aac017 = item["MaritalStatus"].ToString(),
acc217 = item["Years"].ToString(),
//aac183 = "070900",
aac010 = item["Height"].ToString(),
aab301 = "510400000000",
aae011 = item["PersonName"].ToString(),
aae017 = "攀西人才网",
aae036 = DateTime.Now.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss"),
aae396 = DateTime.Now.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss"),
aae022 = "510400000000",
};
//毕业时间
string yac01g = item["GraduationTime"].ToString();
if (string.IsNullOrWhiteSpace(yac01g))
{
personInfo.yac01g = "无";
}
else
{
personInfo.yac01g = yac01g;
}
//毕业院校
string yau002 = item["GraduateSchool"].ToString();
if (yau002 != null && yau002.Length > 200)
{
personInfo.yau002 = yau002.Substring(0, 200);
}
else
{
personInfo.yau002 = "无";
}
//身份证号
string aac002 = item["IDCardNo"].ToString().ToLower();
if (string.IsNullOrWhiteSpace(aac002))
{
continue;
//身份证号为空则不传这条数据
}
else
{
personInfo.aac002 = aac002;
}
string acb501 = item["Mobile"].ToString();
//手机号为空就不传
if (string.IsNullOrWhiteSpace(acb501))
{
continue;
}
else
{
personInfo.acb501 = acb501;
}
//政治面貌
var aac024 = codeMappers.Where(c => item["psItemCode"].ToString()
.Equals(c.localCodeValue)).Where(c => c.codeType.Equals("AAC024")).FirstOrDefault();
if (aac024 == null)
{
//群众
personInfo.aac024 = "13";
}
else
{
personInfo.aac024 = aac024.codeValue.ToString();
}
//民族
var aac005 = codeMappers.Where(c => item["natItemCode"].ToString().
Equals(c.localCodeValue)).FirstOrDefault();
if (aac005 == null)
{
personInfo.aac005 = "01";
}
else
{
personInfo.aac005 = aac005.codeValue.ToString();
}
//文化程度
var aac011 = codeMappers.Where(c => item["degItemCode"].ToString().
Equals(c.localCodeValue)).Where(c => c.codeType.Equals("AAC011")).FirstOrDefault();
if (aac011 == null)
{
personInfo.aac011 = "0";
}
else
{
personInfo.aac011 = aac011.codeValue.ToString();
}
//联系地址
personInfo.aae006 = item["Addr"].ToString();
if (string.IsNullOrEmpty(personInfo.aae006))
{
personInfo.aae006 = "攀枝花市";
}
else
{
personInfo.aae006 = item["Addr"].ToString();
}
personInfo.aae011 = item["PersonName"].ToString();
//经办人
if (string.IsNullOrWhiteSpace(personInfo.aae011))
{
personInfo.aae011 = "攀枝花市人才服务中心";
}
else
{
personInfo.aae011 = item["IDCardNo"].ToString();
}
personInfolist.Add(personInfo);
}
}
DateTimeFormatInfo dtFormat = new DateTimeFormatInfo();
dtFormat.ShortDatePattern = "yyyy/MM/dd";
//需要推送的信息 过滤：未插入，插入但更新时间大于推送时间
List <PersonInfo> personInfos1 = personInfolist.Where(r => !YetInsertInfolist.Any(y => y.number == r.aac001)).ToList();
//List<PersonInfo> personInfos2 = personInfolist.Where(r => YetInsertInfolist.Any(y => y.number == r.aac001 && Convert.ToDateTime(y.updateTime, dtFormat)
//< Convert.ToDateTime(r.aae396, dtFormat))).ToList();
//List<PersonInfo> personInfos = personInfos1.Union(personInfos2).ToList();
return(personInfos1);
}
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);
}
public List <PersonInfo> QueryPersonInfo()
{
string cmdText = @"SELECT LEFT(pbi.AccountID,18)AccountID ,
pbi.PersonName ,
pbi.IDCardNo ,
Sex= case when pbi.Sex='男' then 1 else 2 end ,
nat.ItemCode natItemCode ,
CONVERT(varchar(100), pbi.Birthday, 20)Birthday ,
MaritalStatus =case when pbi.MaritalStatus=0 then 1 when pbi.MaritalStatus=1 then 2 else 4 end ,
ps.ItemCode psItemCode ,
Years = case when we.Years<1 and we.Years>0 then 1 when we.Years<3 then 2
when we.Years>=3 and we.Years<=5 then 3 when we.Years>5 and we.Years<=10 then 4 when we.Years>10 then 5 else 0 end,
deg.ItemCode degItemCode ,
eat.GraduateSchool ,
eat.Major ,
CONVERT(varchar(100), eat.GraduationTime, 20)GraduationTime ,
pbi.Mobile ,
pbi.Height,
ic.Addr
FROM PersonBaseInfo pbi
left JOIN dbo.ItemDetail nat ON nat.ID = pbi.NationID
left JOIN ItemDetail ps ON ps.ID = pbi.PoliticalStatusID
left JOIN dbo.PersonResume res ON res.InfoID = pbi.Id
left JOIN dbo.PersonWorkExperience we ON we.ResumeID = res.Id
left JOIN dbo.PersonEducationAndTraining eat ON eat.ResumeID = res.Id
left JOIN dbo.ItemDetail deg ON eat.HighestDegreeID = deg.ID
left JOIN (SELECT c1.ID ,
c2.ItemName + c1.ItemName Addr
FROM dbo.ItemDetail c1 ,
dbo.ItemDetail c2
WHERE c1.ParentID = CONVERT(VARCHAR(50),c2.ID))ic
ON ic.id=pbi.PermanentResidenceID
WHERE res.IsAudited=1";
DataTable personTable = SqlHelper.ExecuteDataTable(cmdText, new SqlParameter("@param", DBNull.Value));
List <CodeMapper> codeMappers = SqlHelper.QueryCodeMapper();
string cmdText2 = "select * from PullInfoRecord where type='个人信息'";
DataTable yetpersonInfoTable = SqlHelper.ExecuteDataTable(cmdText2, new SqlParameter("@param", DBNull.Value));
List <YetInsertInfo> YetInsertInfolist = new List <YetInsertInfo>();
List <PersonInfo> personInfolist = new List <PersonInfo>();
if (yetpersonInfoTable.Rows.Count > 0)
{
foreach (DataRow item in yetpersonInfoTable.Rows)
{
YetInsertInfo yetInsertInfo = new YetInsertInfo()
{
number = item["number"].ToString(),
type = item["type"].ToString(),
updateTime = item["updateTime"].ToString()
};
YetInsertInfolist.Add(yetInsertInfo);
}
}
if (personTable.Rows.Count > 0)
{
List <PersonInfo> list = new List <PersonInfo>();
foreach (DataRow item in personTable.Rows)
{
PersonInfo personInfo = new PersonInfo()
{
aac001 = item["AccountID"].ToString(),
aac003 = item["PersonName"].ToString(),
aac004 = item["Sex"].ToString(),
aac006 = item["Birthday"].ToString(),
aac017 = item["MaritalStatus"].ToString(),
acc217 = item["Years"].ToString(),
yau002 = item["GraduateSchool"].ToString(),
//aac183 = "070900",
yac01g = item["GraduationTime"].ToString(),
acb501 = item["Mobile"].ToString(),
aac010 = item["Height"].ToString(),
aab301 = "510400000000",
aae017 = "攀西人才网",
aae036 = DateTime.Now.ToLocalTime().ToString(),
aae396 = DateTime.Now.ToLocalTime().ToString(),
aae022 = "510400000000",
};
//政治面貌
var aac024 = codeMappers.Where(c => item["psItemCode"].ToString()
.Equals(c.localCodeValue)).FirstOrDefault();
if (aac024 == null)
{
personInfo.aac024 = "0";
}
else
{
personInfo.aac024 = aac024.codeValue.ToString();
}
//民族
var aac005 = codeMappers.Where(c => item["natItemCode"].ToString().
Equals(c.localCodeValue)).FirstOrDefault();
if (aac005 == null)
{
personInfo.aac005 = "01";
}
else
{
personInfo.aac005 = aac005.codeValue.ToString();
}
//文化程度
var aac011 = codeMappers.Where(c => item["degItemCode"].ToString().
Equals(c.localCodeValue)).FirstOrDefault();
if (aac011 == null)
{
personInfo.aac011 = "0";
}
else
{
personInfo.aac011 = aac011.codeValue.ToString();
}
//联系地址
personInfo.aae006 = item["Addr"].ToString();
if (string.IsNullOrEmpty(personInfo.aae006))
{
personInfo.aae006 = "广元市";
}
else
{
personInfo.aae006 = item["Addr"].ToString();
}
personInfo.aac002 = item["IDCardNo"].ToString();
//身份证
personInfo.aac002 = item["IDCardNo"].ToString().ToLower();
personInfo.aae011 = item["PersonName"].ToString();
//经办人
if (string.IsNullOrEmpty(personInfo.aae011))
{
personInfo.aae011 = "广元市人才服务中心";
}
else
{
personInfo.aae011 = item["IDCardNo"].ToString();
}
personInfolist.Add(personInfo);
}
}
DateTimeFormatInfo dtFormat = new DateTimeFormatInfo();
dtFormat.ShortDatePattern = "yyyy/MM/dd";
//需要推送的信息 过滤：未插入，插入但更新时间大于推送时间
List <PersonInfo> personInfos1 = personInfolist.Where(r => !YetInsertInfolist.Any(y => y.number == r.aac001)).ToList();
List <PersonInfo> personInfos2 = personInfolist.Where(r => YetInsertInfolist.Any(y => y.number == r.aac001 && Convert.ToDateTime(y.updateTime, dtFormat)
< Convert.ToDateTime(r.aae396, dtFormat))).ToList();
List <PersonInfo> personInfos = personInfos1.Union(personInfos2).ToList();
return(personInfos);
}