public void updataMsg(DataTable msg, string emploreeid)
{
string sql = " SELECT id,org,deptName FROM dbo.T_dept ";
DataTable dt = new DataTable();
dt = SabrinaVisa_SqlHelper.ExcuteTable(sql);
}
public DataTable getDetps(string org)
{
string sql = " SELECT id,org,deptName FROM dbo.T_dept WHERE org ='" + org + "'";
DataTable dt = new DataTable();
dt = SabrinaVisa_SqlHelper.ExcuteTable(sql);
return(dt);
}
public DataTable getWorkIDs()
{
string sql = " SELECT workID FROM T_employee";
DataTable dt = new DataTable();
dt = SabrinaVisa_SqlHelper.ExcuteTable(sql);
return(dt);
}
public DataTable getJobs(string org)
{
string sql = " SELECT positionName FROM T_Position WHERE Org = '"+ org + "';";
DataTable dt = new DataTable();
dt = SabrinaVisa_SqlHelper.ExcuteTable(sql);
return(dt);
}
public DataTable getPassportNumbers()
{
string sql = "SELECT passportNumber FROM dbo.T_certified";
DataTable dt = new DataTable();
dt = SabrinaVisa_SqlHelper.ExcuteTable(sql);
return(dt);
}
public DataTable getEducation()
{
string sql = " SELECT id,educationName,educationNameEN,educationNote FROM dbo.T_education ";
DataTable dt = new DataTable();
dt = SabrinaVisa_SqlHelper.ExcuteTable(sql);
return(dt);
}
public DataTable getSex()
{
string sql = " SELECT id,sexID,sexName,sexNote FROM dbo.T_Sex ";
DataTable dt = new DataTable();
dt = SabrinaVisa_SqlHelper.ExcuteTable(sql);
return(dt);
}
public DataTable getPositions(string org)
{
string sql = "SELECT id,positionName,positionNameEN,Org FROM dbo.T_Position WHERE org ='" + org + "'";
DataTable dt = new DataTable();
dt = SabrinaVisa_SqlHelper.ExcuteTable(sql);
return(dt);
}
// public string insetCertified(DataTable certified)
// {
// string mesg= SabrinaVisa_SqlHelper.SqlBulkCopyCertified(certified);
// return mesg;
// }
public int getCertifiedMaxID()
{
string sql = @"SELECT MAX(id) id FROM T_certified";
DataTable dt = SabrinaVisa_SqlHelper.ExcuteTable(sql);
if (dt.Rows.Count <= 0)
{
return(0);
}
string maxID = dt.Rows[0][0].ToString();
return(Convert.ToInt32(maxID));
}
public DataTable getEmployeesByParameters(EmployeesParameters ep)
{
string sql = "";
if (ep.checkDate) //考核月份
{
if (ep.dateRequire == 0) //考核月份
{
sql = @"
SELECT e.id AS Eid, e.passportNumber, e.deptID, e.subID, e.workID, e.userName, e.userNameEN, e.userSexID, e.birthday, e.educationID,
e.hometown, e.phoneNumber, e.positionID, e.entryDate, e.jobChange, e.assessDate, e.contractFinishDate, e.tryFinishDate,
e.planResignDate, e.resignDate, e.resignNote, e.resigned,
d.id AS Did, d.org, d.deptName,
c.id AS Cid, c.passportNumber, c.passportIssueDate, c.passportFinishDate, c.passportSignArea, c.passportVisaNumber, c.passportVisaArea,
c.passportVisaTimeLimit, c.passportVisaFinshDate, c.entryVisaDate, c.workerCard, c.workerCardID, c.healthCard,
m.id AS Mid, m.workID, m.msgTxt, m.msgCheck,
p.id AS Pid, p.positionName, p.positionNameEN, p.Org,
s.sexID, s.sexName, s.sexNote
FROM dbo.T_employee e
LEFT JOIN dbo.T_dept d ON e.deptID = d.id
LEFT JOIN dbo.T_certified c ON e.passportNumber = c.passportNumber
LEFT JOIN dbo.T_msg m ON m.workID = e.workID
LEFT JOIN dbo.T_education u ON u.id = e.educationID
LEFT JOIN dbo.T_Position p ON p.id = e.positionID
LEFT JOIN dbo.T_Sex s ON s.sexID = e.userSexID
WHERE 1 = 1
AND d.org LIKE '%" + ep.org + @"%'
AND d.deptName LIKE '%" + ep.dept + @"%'
AND e.passportNumber LIKE '%" + ep.passPortNumber + @"%'
AND e.userName LIKE '%" + ep.userName + @"%'
AND e.workID LIKE '%" + ep.workNumber + @"%'
AND e.assessDate ='" + ep.assessDate + "'";
}
else if (ep.dateRequire == 1) //合约到期日
{
sql = @"
SELECT e.id AS Eid, e.passportNumber, e.deptID, e.subID, e.workID, e.userName, e.userNameEN, e.userSexID, e.birthday, e.educationID,
e.hometown, e.phoneNumber, e.positionID, e.entryDate, e.jobChange, e.assessDate, e.contractFinishDate, e.tryFinishDate,
e.planResignDate, e.resignDate, e.resignNote, e.resigned,
d.id AS Did, d.org, d.deptName,
c.id AS Cid, c.passportNumber, c.passportIssueDate, c.passportFinishDate, c.passportSignArea, c.passportVisaNumber, c.passportVisaArea,
c.passportVisaTimeLimit, c.passportVisaFinshDate, c.entryVisaDate, c.workerCard, c.workerCardID, c.healthCard,
m.id AS Mid, m.workID, m.msgTxt, m.msgCheck,
p.id AS Pid, p.positionName, p.positionNameEN, p.Org,
s.sexID, s.sexName, s.sexNote
FROM dbo.T_employee e
LEFT JOIN dbo.T_dept d ON e.deptID = d.id
LEFT JOIN dbo.T_certified c ON e.passportNumber = c.passportNumber
LEFT JOIN dbo.T_msg m ON m.workID = e.workID
LEFT JOIN dbo.T_education u ON u.id = e.educationID
LEFT JOIN dbo.T_Position p ON p.id = e.positionID
LEFT JOIN dbo.T_Sex s ON s.sexID = e.userSexID
WHERE 1 = 1
AND d.org LIKE '%" + ep.org + @"%'
AND d.deptName LIKE '%" + ep.dept + @"%'
AND e.passportNumber LIKE '%" + ep.passPortNumber + @"%'
AND e.userName LIKE '%" + ep.userName + @"%'
AND e.workID LIKE '%" + ep.workNumber + @"%'
AND e.contractFinishDate BETWEEN '" + ep.starDate + "' AND '" + ep.stopDate + "'";
}
else if (ep.dateRequire == 2) //试用到期日
{
sql = @"
SELECT e.id AS Eid, e.passportNumber, e.deptID, e.subID, e.workID, e.userName, e.userNameEN, e.userSexID, e.birthday, e.educationID,
e.hometown, e.phoneNumber, e.positionID, e.entryDate, e.jobChange, e.assessDate, e.contractFinishDate, e.tryFinishDate,
e.planResignDate, e.resignDate, e.resignNote, e.resigned,
d.id AS Did, d.org, d.deptName,
c.id AS Cid, c.passportNumber, c.passportIssueDate, c.passportFinishDate, c.passportSignArea, c.passportVisaNumber, c.passportVisaArea,
c.passportVisaTimeLimit, c.passportVisaFinshDate, c.entryVisaDate, c.workerCard, c.workerCardID, c.healthCard,
m.id AS Mid, m.workID, m.msgTxt, m.msgCheck,
p.id AS Pid, p.positionName, p.positionNameEN, p.Org,
s.sexID, s.sexName, s.sexNote
FROM dbo.T_employee e
LEFT JOIN dbo.T_dept d ON e.deptID = d.id
LEFT JOIN dbo.T_certified c ON e.passportNumber = c.passportNumber
LEFT JOIN dbo.T_msg m ON m.workID = e.workID
LEFT JOIN dbo.T_education u ON u.id = e.educationID
LEFT JOIN dbo.T_Position p ON p.id = e.positionID
LEFT JOIN dbo.T_Sex s ON s.sexID = e.userSexID
WHERE 1 = 1
AND d.org LIKE '%" + ep.org + @"%'
AND d.deptName LIKE '%" + ep.dept + @"%'
AND e.passportNumber LIKE '%" + ep.passPortNumber + @"%'
AND e.userName LIKE '%" + ep.userName + @"%'
AND e.workID LIKE '%" + ep.workNumber + @"%'
AND e.tryFinishDate BETWEEN '" + ep.starDate + "' AND '" + ep.stopDate + "'";
}
else if (ep.dateRequire == 3) //护照到期日
{
sql = @"
SELECT e.id AS Eid, e.passportNumber, e.deptID, e.subID, e.workID, e.userName, e.userNameEN, e.userSexID, e.birthday, e.educationID,
e.hometown, e.phoneNumber, e.positionID, e.entryDate, e.jobChange, e.assessDate, e.contractFinishDate, e.tryFinishDate,
e.planResignDate, e.resignDate, e.resignNote, e.resigned,
d.id AS Did, d.org, d.deptName,
c.id AS Cid, c.passportNumber, c.passportIssueDate, c.passportFinishDate, c.passportSignArea, c.passportVisaNumber, c.passportVisaArea,
c.passportVisaTimeLimit, c.passportVisaFinshDate, c.entryVisaDate, c.workerCard, c.workerCardID, c.healthCard,
m.id AS Mid, m.workID, m.msgTxt, m.msgCheck,
p.id AS Pid, p.positionName, p.positionNameEN, p.Org,
s.sexID, s.sexName, s.sexNote
FROM dbo.T_employee e
LEFT JOIN dbo.T_dept d ON e.deptID = d.id
LEFT JOIN dbo.T_certified c ON e.passportNumber = c.passportNumber
LEFT JOIN dbo.T_msg m ON m.workID = e.workID
LEFT JOIN dbo.T_education u ON u.id = e.educationID
LEFT JOIN dbo.T_Position p ON p.id = e.positionID
LEFT JOIN dbo.T_Sex s ON s.sexID = e.userSexID
WHERE 1 = 1
AND d.org LIKE '%" + ep.org + @"%'
AND d.deptName LIKE '%" + ep.dept + @"%'
AND e.passportNumber LIKE '%" + ep.passPortNumber + @"%'
AND e.userName LIKE '%" + ep.userName + @"%'
AND e.workID LIKE '%" + ep.workNumber + @"%'
AND c.passportFinishDate BETWEEN '" + ep.starDate + "' AND '" + ep.stopDate + "'";
}
else if (ep.dateRequire == 4) //签证到期日
{
sql = @"
SELECT e.id AS Eid, e.passportNumber, e.deptID, e.subID, e.workID, e.userName, e.userNameEN, e.userSexID, e.birthday, e.educationID,
e.hometown, e.phoneNumber, e.positionID, e.entryDate, e.jobChange, e.assessDate, e.contractFinishDate, e.tryFinishDate,
e.planResignDate, e.resignDate, e.resignNote, e.resigned,
d.id AS Did, d.org, d.deptName,
c.id AS Cid, c.passportNumber, c.passportIssueDate, c.passportFinishDate, c.passportSignArea, c.passportVisaNumber, c.passportVisaArea,
c.passportVisaTimeLimit, c.passportVisaFinshDate, c.entryVisaDate, c.workerCard, c.workerCardID, c.healthCard,
m.id AS Mid, m.workID, m.msgTxt, m.msgCheck,
p.id AS Pid, p.positionName, p.positionNameEN, p.Org,
s.sexID, s.sexName, s.sexNote
FROM dbo.T_employee e
LEFT JOIN dbo.T_dept d ON e.deptID = d.id
LEFT JOIN dbo.T_certified c ON e.passportNumber = c.passportNumber
LEFT JOIN dbo.T_msg m ON m.workID = e.workID
LEFT JOIN dbo.T_education u ON u.id = e.educationID
LEFT JOIN dbo.T_Position p ON p.id = e.positionID
LEFT JOIN dbo.T_Sex s ON s.sexID = e.userSexID
WHERE 1 = 1
AND d.org LIKE '%" + ep.org + @"%'
AND d.deptName LIKE '%" + ep.dept + @"%'
AND e.passportNumber LIKE '%" + ep.passPortNumber + @"%'
AND e.userName LIKE '%" + ep.userName + @"%'
AND e.workID LIKE '%" + ep.workNumber + @"%'
AND c.passportVisaFinshDate BETWEEN '" + ep.starDate + "' AND '" + ep.stopDate + "'";
}
else if (ep.dateRequire == 5) //入职签证日
{
sql = @"
SELECT e.id AS Eid, e.passportNumber, e.deptID, e.subID, e.workID, e.userName, e.userNameEN, e.userSexID, e.birthday, e.educationID,
e.hometown, e.phoneNumber, e.positionID, e.entryDate, e.jobChange, e.assessDate, e.contractFinishDate, e.tryFinishDate,
e.planResignDate, e.resignDate, e.resignNote, e.resigned,
d.id AS Did, d.org, d.deptName,
c.id AS Cid, c.passportNumber, c.passportIssueDate, c.passportFinishDate, c.passportSignArea, c.passportVisaNumber, c.passportVisaArea,
c.passportVisaTimeLimit, c.passportVisaFinshDate, c.entryVisaDate, c.workerCard, c.workerCardID, c.healthCard,
m.id AS Mid, m.workID, m.msgTxt, m.msgCheck,
p.id AS Pid, p.positionName, p.positionNameEN, p.Org,
s.sexID, s.sexName, s.sexNote
FROM dbo.T_employee e
LEFT JOIN dbo.T_dept d ON e.deptID = d.id
LEFT JOIN dbo.T_certified c ON e.passportNumber = c.passportNumber
LEFT JOIN dbo.T_msg m ON m.workID = e.workID
LEFT JOIN dbo.T_education u ON u.id = e.educationID
LEFT JOIN dbo.T_Position p ON p.id = e.positionID
LEFT JOIN dbo.T_Sex s ON s.sexID = e.userSexID
WHERE 1 = 1
AND d.org LIKE '%" + ep.org + @"%'
AND d.deptName LIKE '%" + ep.dept + @"%'
AND e.passportNumber LIKE '%" + ep.passPortNumber + @"%'
AND e.userName LIKE '%" + ep.userName + @"%'
AND e.workID LIKE '%" + ep.workNumber + @"%'
AND c.entryVisaDate BETWEEN '" + ep.starDate + "' AND '" + ep.stopDate + "'";
}
else if (ep.dateRequire == 6) //入职日期
{
sql = @"
SELECT e.id AS Eid, e.passportNumber, e.deptID, e.subID, e.workID, e.userName, e.userNameEN, e.userSexID, e.birthday, e.educationID,
e.hometown, e.phoneNumber, e.positionID, e.entryDate, e.jobChange, e.assessDate, e.contractFinishDate, e.tryFinishDate,
e.planResignDate, e.resignDate, e.resignNote, e.resigned,
d.id AS Did, d.org, d.deptName,
c.id AS Cid, c.passportNumber, c.passportIssueDate, c.passportFinishDate, c.passportSignArea, c.passportVisaNumber, c.passportVisaArea,
c.passportVisaTimeLimit, c.passportVisaFinshDate, c.entryVisaDate, c.workerCard, c.workerCardID, c.healthCard,
m.id AS Mid, m.workID, m.msgTxt, m.msgCheck,
p.id AS Pid, p.positionName, p.positionNameEN, p.Org,
s.sexID, s.sexName, s.sexNote
FROM dbo.T_employee e
LEFT JOIN dbo.T_dept d ON e.deptID = d.id
LEFT JOIN dbo.T_certified c ON e.passportNumber = c.passportNumber
LEFT JOIN dbo.T_msg m ON m.workID = e.workID
LEFT JOIN dbo.T_education u ON u.id = e.educationID
LEFT JOIN dbo.T_Position p ON p.id = e.positionID
LEFT JOIN dbo.T_Sex s ON s.sexID = e.userSexID
WHERE 1 = 1
AND d.org LIKE '%" + ep.org + @"%'
AND d.deptName LIKE '%" + ep.dept + @"%'
AND e.passportNumber LIKE '%" + ep.passPortNumber + @"%'
AND e.userName LIKE '%" + ep.userName + @"%'
AND e.workID LIKE '%" + ep.workNumber + @"%'
AND e.entryDate BETWEEN '" + ep.starDate + "' AND '" + ep.stopDate + "'";
}
else if (ep.dateRequire == 7) //预计离职日
{
sql = @"
SELECT e.id AS Eid, e.passportNumber, e.deptID, e.subID, e.workID, e.userName, e.userNameEN, e.userSexID, e.birthday, e.educationID,
e.hometown, e.phoneNumber, e.positionID, e.entryDate, e.jobChange, e.assessDate, e.contractFinishDate, e.tryFinishDate,
e.planResignDate, e.resignDate, e.resignNote, e.resigned,
d.id AS Did, d.org, d.deptName,
c.id AS Cid, c.passportNumber, c.passportIssueDate, c.passportFinishDate, c.passportSignArea, c.passportVisaNumber, c.passportVisaArea,
c.passportVisaTimeLimit, c.passportVisaFinshDate, c.entryVisaDate, c.workerCard, c.workerCardID, c.healthCard,
m.id AS Mid, m.workID, m.msgTxt, m.msgCheck,
p.id AS Pid, p.positionName, p.positionNameEN, p.Org,
s.sexID, s.sexName, s.sexNote
FROM dbo.T_employee e
LEFT JOIN dbo.T_dept d ON e.deptID = d.id
LEFT JOIN dbo.T_certified c ON e.passportNumber = c.passportNumber
LEFT JOIN dbo.T_msg m ON m.workID = e.workID
LEFT JOIN dbo.T_education u ON u.id = e.educationID
LEFT JOIN dbo.T_Position p ON p.id = e.positionID
LEFT JOIN dbo.T_Sex s ON s.sexID = e.userSexID
WHERE 1 = 1
AND d.org LIKE '%" + ep.org + @"%'
AND d.deptName LIKE '%" + ep.dept + @"%'
AND e.passportNumber LIKE '%" + ep.passPortNumber + @"%'
AND e.userName LIKE '%" + ep.userName + @"%'
AND e.workID LIKE '%" + ep.workNumber + @"%'
AND e.planResignDate BETWEEN '" + ep.starDate + "' AND '" + ep.stopDate + "'";
}
else if (ep.dateRequire == 8) //离职日期
{
sql = @"
SELECT e.id AS Eid, e.passportNumber, e.deptID, e.subID, e.workID, e.userName, e.userNameEN, e.userSexID, e.birthday, e.educationID,
e.hometown, e.phoneNumber, e.positionID, e.entryDate, e.jobChange, e.assessDate, e.contractFinishDate, e.tryFinishDate,
e.planResignDate, e.resignDate, e.resignNote, e.resigned,
d.id AS Did, d.org, d.deptName,
c.id AS Cid, c.passportNumber, c.passportIssueDate, c.passportFinishDate, c.passportSignArea, c.passportVisaNumber, c.passportVisaArea,
c.passportVisaTimeLimit, c.passportVisaFinshDate, c.entryVisaDate, c.workerCard, c.workerCardID, c.healthCard,
m.id AS Mid, m.workID, m.msgTxt, m.msgCheck,
p.id AS Pid, p.positionName, p.positionNameEN, p.Org,
s.sexID, s.sexName, s.sexNote
FROM dbo.T_employee e
LEFT JOIN dbo.T_dept d ON e.deptID = d.id
LEFT JOIN dbo.T_certified c ON e.passportNumber = c.passportNumber
LEFT JOIN dbo.T_msg m ON m.workID = e.workID
LEFT JOIN dbo.T_education u ON u.id = e.educationID
LEFT JOIN dbo.T_Position p ON p.id = e.positionID
LEFT JOIN dbo.T_Sex s ON s.sexID = e.userSexID
WHERE 1 = 1
AND d.org LIKE '%" + ep.org + @"%'
AND d.deptName LIKE '%" + ep.dept + @"%'
AND e.passportNumber LIKE '%" + ep.passPortNumber + @"%'
AND e.userName LIKE '%" + ep.userName + @"%'
AND e.workID LIKE '%" + ep.workNumber + @"%'
AND e.resignDate BETWEEN '" + ep.starDate + "' AND '" + ep.stopDate + "'";
}
}
else
{
sql = @"
SELECT e.id AS Eid, e.passportNumber, e.deptID, e.subID, e.workID, e.userName, e.userNameEN, e.userSexID, e.birthday, e.educationID,
e.hometown, e.phoneNumber, e.positionID, e.entryDate, e.jobChange, e.assessDate, e.contractFinishDate, e.tryFinishDate,
e.planResignDate, e.resignDate, e.resignNote, e.resigned,
d.id AS Did, d.org, d.deptName,
c.id AS Cid, c.passportNumber, c.passportIssueDate, c.passportFinishDate, c.passportSignArea, c.passportVisaNumber, c.passportVisaArea,
c.passportVisaTimeLimit, c.passportVisaFinshDate, c.entryVisaDate, c.workerCard, c.workerCardID, c.healthCard,
m.id AS Mid, m.workID, m.msgTxt, m.msgCheck,
p.id AS Pid, p.positionName, p.positionNameEN, p.Org,
s.sexID, s.sexName, s.sexNote
FROM dbo.T_employee e
LEFT JOIN dbo.T_dept d ON e.deptID = d.id
LEFT JOIN dbo.T_certified c ON e.passportNumber = c.passportNumber
LEFT JOIN dbo.T_msg m ON m.workID = e.workID
LEFT JOIN dbo.T_education u ON u.id = e.educationID
LEFT JOIN dbo.T_Position p ON p.id = e.positionID
LEFT JOIN dbo.T_Sex s ON s.sexID = e.userSexID
WHERE 1 = 1
AND d.org LIKE '%" + ep.org + @"%'
AND d.deptName LIKE '%" + ep.dept + @"%'
AND e.passportNumber LIKE '%" + ep.passPortNumber + @"%'
AND e.userName LIKE '%" + ep.userName + @"%'
AND e.workID LIKE '%" + ep.workNumber + @"%' ";
}
DataTable dt = new DataTable();
dt = SabrinaVisa_SqlHelper.ExcuteTable(sql);
return(dt);
}