public void InsertOrUpdate(EmployeesParameters employeesParameters)
{
Employees employees = new Employees
{
Id = employeesParameters.Id,
FirstName = employeesParameters.FirstName,
LastName = employeesParameters.LastName,
DOB = employeesParameters.Dob,
Batch = employeesParameters.Batch,
GenderId = employeesParameters.GenderId,
StreamId = employeesParameters.Stream,
QuarterId = employeesParameters.QuarterId
};
if (employeesParameters.Id == null)
{
_context.EmployeesTable.Add(employees);
_context.SaveChanges();
}
else
{
var result = _context.EmployeesTable.SingleOrDefault(e => e.Id == employeesParameters.Id);
result.FirstName = employees.FirstName;
result.LastName = employees.LastName;
result.GenderId = employees.GenderId;
result.DOB = employees.DOB;
result.Batch = employees.Batch;
result.QuarterId = employees.QuarterId;
result.StreamId = employees.StreamId;
_context.SaveChanges();
}
}
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);
}
public List <Employees> getEmployeesByParameters(EmployeesParameters ep)
{
DataTable employees = es.getEmployeesByParameters(ep);
if (employees.Rows.Count <= 0)
{
return(null);
}
List <Employees> eps = new List <Employees>();
foreach (DataRow item in employees.Rows)
{
Employees epl = new Employees();
epl.Eid = Convert.ToInt32(item["Eid"].ToString());
epl.passportNumber = item["passportNumber"].ToString();
epl.Did = Convert.ToInt32(item["deptID"].ToString());
epl.subID = item["subID"].ToString();
epl.workID = item["workID"].ToString();
epl.userName = item["userName"].ToString();
epl.userNameEN = item["userNameEN"].ToString();
epl.userSexID = Convert.ToInt32(item["userSexID"].ToString());
epl.birthday = item["birthday"].ToString();
epl.educationID = Convert.ToInt32(item["educationID"].ToString());
epl.hometown = item["hometown"].ToString();
epl.phoneNumber = item["phoneNumber"].ToString();
epl.positionID = Convert.ToInt32(item["positionID"].ToString());
epl.entryDate = item["entryDate"].ToString();
epl.jobChange = item["jobChange"].ToString();
epl.assessDate = item["assessDate"].ToString();
epl.contractFinishDate = item["contractFinishDate"].ToString();
epl.tryFinishDate = item["tryFinishDate"].ToString();
epl.planResignDate = item["planResignDate"].ToString();
epl.resignDate = item["resignDate"].ToString();
epl.resignNote = item["resignNote"].ToString();
string r = item["resigned"].ToString();
epl.resigned = r.Length == 0 ? 0 : Convert.ToInt32(r);
epl.Did = Convert.ToInt32(item["Did"].ToString());
epl.Org = item["org"].ToString();
epl.deptName = item["deptName"].ToString();
epl.Cid = Convert.ToInt32(item["Cid"].ToString());
epl.passportIssueDate = item["passportIssueDate"].ToString();
epl.passportFinishDate = item["passportFinishDate"].ToString();
epl.passportSignArea = item["passportSignArea"].ToString();
epl.passportVisaNumber = item["passportVisaNumber"].ToString();
epl.passportVisaArea = item["passportVisaArea"].ToString();
epl.passportVisaTimeLimit = item["passportVisaTimeLimit"].ToString();
epl.passportVisaFinshDate = item["passportVisaFinshDate"].ToString();
epl.entryVisaDate = item["entryVisaDate"].ToString();
string w = item["workerCard"].ToString();
epl.workerCard = w.Length == 0 ? 0 : Convert.ToInt32(w);
epl.workerCardID = item["workerCardID"].ToString();
string h = item["healthCard"].ToString();
epl.healthCard = h.Length == 0 ? 0 : Convert.ToInt32(h);
string mid = item["Mid"].ToString();
if (mid == "")
{
epl.Mid = -1;
}
else
{
epl.Mid = Convert.ToInt32(mid);
}
epl.msgTxt = item["msgTxt"].ToString();
string m = item["msgCheck"].ToString();
epl.msgCheck = m.Length == 0 ? 0 : Convert.ToInt32(m);
epl.Pid = Convert.ToInt32(item["Pid"].ToString());
epl.positionName = item["positionName"].ToString();
epl.positionNameEN = item["positionNameEN"].ToString();
epl.sexID = Convert.ToInt32(item["sexID"].ToString());
epl.sexName = item["sexName"].ToString();
epl.sexNote = item["sexNote"].ToString();
eps.Add(epl);
}
return(eps);
}