/// <summary>
///
/// </summary>
/// <param name=></param>
/// <returns></returns>
public List <MODEL.HR_Employee> GetHREmployee(string aid)
{
string sql = @"
select *
from HR_Employee where AID=@AID order by EnrollNo
";
SqlParameter[] ps =
{
new SqlParameter("@AID", aid)
};
DataTable dt = SqlHelper.ExcuteTable(sql, ps);
List <MODEL.HR_Employee> lists = null;
if (dt.Rows.Count > 0)
{
lists = new List <MODEL.HR_Employee>();
foreach (DataRow row in dt.Rows)
{
MODEL.HR_Employee c = new MODEL.HR_Employee();
LoadDataToList1(row, c);
lists.Add(c);
}
}
return(lists);
}
/// 增加人员
/// </summary>
/// <param name="addStyleBase"></param>
/// <returns></returns>
public int UpdateHREmployee(MODEL.HR_Employee emp)
{
return(ems.UpdateHREmployee(emp));
}
/// 增加人员
/// </summary>
/// <param name="addStyleBase"></param>
/// <returns></returns>
public int AddHREmployee(MODEL.HR_Employee emp)
{
return(ems.AddHREmployee(emp));
}
/// 增加人员
/// </summary>
/// <param name="addStyleBase"></param>
/// <returns></returns>
public int UpdateHREmployee(MODEL.HR_Employee emp)
{
string sql = @"
UPDATE [dbo].[HR_Employee]
Set AID=@AID, DeptID=@DeptID,EmpID=@EmpID,Title=@Title,Salary=@Salary,Job=@Job,
EnrollNo=@EnrollNo,Accountno=@Accountno,NationalID=@NationalID,JobID=@JobID,ProvinceID=@ProvinceID,
ShiftID=@ShiftID,CallName=@CallName,KhName=@KhName,KLName=@KLName,KName=@KName,ELName=@ELName,EName=@EName,
Gender=@Gender,Status=@Status,PersonalID=@PersonalID,NSSF=@NSSF,Education=@Education,
WorkBook=@WorkBook,CardNo=@CardNo,Address=@Address,PrePaid=@PrePaid,Paid=@Paid,
IsPermanent=@IsPermanent,IsContract=@IsContract,IsResign=@IsResign,ResignType=@ResignType,ResignReason=@ResignReason,ResignDate=@ResignDate,
BirthDate=@BirthDate,CreateDate=@CreateDate,HireDate=@HireDate,KeepFrom=@KeepFrom,BackWork=@BackWork,
Union1=@Union1,Union2=@Union2,Union3=@Union3,Union4=@Union4,Union5=@Union5,Union6=@Union6,Union7=@Union7,Union8=@Union8,Union9=@Union9,Union10=@Union10,Union11=@Union11,Union12=@Union12,
Photo=@Photo,isDel=@isDel
WHERE Guid=@Guid
";
SqlParameter[] ps =
{
new SqlParameter("@Guid", SqlDbType.UniqueIdentifier)
{
Value = SqlHelper.ToDbValue(emp.Guid)
},
new SqlParameter("@AID", SqlDbType.Decimal)
{
Value = SqlHelper.ToDbValue(emp.AID)
},
new SqlParameter("DeptID", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.DeptID)
},
new SqlParameter("@EmpID", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.EmpID)
},
new SqlParameter("@Title", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.Title)
},
new SqlParameter("@Salary", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.Salary)
},
new SqlParameter("@Job", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.Job)
},
new SqlParameter("@EnrollNo", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.EnrollNo)
},
new SqlParameter("@Accountno", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Accountno)
},
new SqlParameter("@NationalID", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.NationalID)
},
new SqlParameter("@JobID", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.JobID)
},
new SqlParameter("@ProvinceID", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.ProvinceID)
},
new SqlParameter("@ShiftID", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.ShiftID)
},
new SqlParameter("@CallName", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.CallName)
},
new SqlParameter("@KhName", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.KhName)
},
new SqlParameter("@KLName", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.KLName)
},
new SqlParameter("@KName", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.KName)
},
new SqlParameter("@ELName", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.ELName)
},
new SqlParameter("@EName", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.EName)
},
new SqlParameter("@Gender", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.Gender)
},
new SqlParameter("@Status", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.Status)
},
new SqlParameter("@PersonalID", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.PersonalID)
},
new SqlParameter("@NSSF", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.NSSF)
},
new SqlParameter("@Education", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.Education)
},
new SqlParameter("@WorkBook", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.WorkBook)
},
new SqlParameter("@CardNo", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.CardNo)
},
new SqlParameter("@Address", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.Address)
},
new SqlParameter("@PrePaid", SqlDbType.Bit)
{
Value = SqlHelper.ToDbValue(emp.PrePaid)
},
new SqlParameter("@Paid", SqlDbType.Bit)
{
Value = SqlHelper.ToDbValue(emp.Paid)
},
new SqlParameter("@IsPermanent", SqlDbType.Bit)
{
Value = SqlHelper.ToDbValue(emp.IsPermanent)
},
new SqlParameter("@IsContract", SqlDbType.Bit)
{
Value = SqlHelper.ToDbValue(emp.IsContract)
},
new SqlParameter("@IsResign", SqlDbType.Bit)
{
Value = SqlHelper.ToDbValue(emp.IsResign)
},
new SqlParameter("@ResignType", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.ResignType)
},
new SqlParameter("@ResignReason", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.ResignReason)
},
new SqlParameter("@ResignDate", SqlDbType.Date)
{
Value = SqlHelper.ToDbValue(emp.ResignDate)
},
new SqlParameter("@BirthDate", SqlDbType.Date)
{
Value = SqlHelper.ToDbValue(emp.BirthDate)
},
new SqlParameter("@CreateDate", SqlDbType.Date)
{
Value = SqlHelper.ToDbValue(emp.CreateDate)
},
new SqlParameter("@HireDate", SqlDbType.Date)
{
Value = SqlHelper.ToDbValue(emp.HireDate)
},
new SqlParameter("@KeepFrom", SqlDbType.Date)
{
Value = SqlHelper.ToDbValue(emp.KeepFrom)
},
new SqlParameter("@BackWork", SqlDbType.Date)
{
Value = SqlHelper.ToDbValue(emp.BackWork)
},
new SqlParameter("@Union1", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union1)
},
new SqlParameter("@Union2", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union2)
},
new SqlParameter("@Union3", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union3)
},
new SqlParameter("@Union4", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union4)
},
new SqlParameter("@Union5", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union5)
},
new SqlParameter("@Union6", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union6)
},
new SqlParameter("@Union7", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union7)
},
new SqlParameter("@Union8", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union8)
},
new SqlParameter("@Union9", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union9)
},
new SqlParameter("@Union10", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union10)
},
new SqlParameter("@Union11", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union11)
},
new SqlParameter("@Union12", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union12)
},
new SqlParameter("@Photo", SqlDbType.Image)
{
Value = SqlHelper.ToDbValue(emp.Photo)
},
new SqlParameter("@isDel", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.isDel)
}
};
return(SqlHelper.ExecuteNonQuery(sql, ps));
}
/// 增加人员
/// </summary>
/// <param name="addStyleBase"></param>
/// <returns></returns>
public int AddHREmployee(MODEL.HR_Employee emp)
{
//string sql = "";
string sql = @"
INSERT INTO [dbo].[HR_Employee]
([AID],[DeptID],[EmpID],[Title],[Salary],[Job]
,[EnrollNo],[Accountno],[NationalID],[JobID]
,[ProvinceID],[ShiftID],[CallName],[KhName],[KLName],[KName],[ELName],[EName]
,[Gender],[Status],[PersonalID],[NSSF],[Education]
,[WorkBook],[CardNo],[Address],[PrePaid],[Paid]
,[IsPermanent],[IsContract],[IsResign],[ResignType],[ResignReason],[ResignDate],[BirthDate],[CreateDate]
,[Union1],[Union2],[Union3],[Union4],[Union5],[Union6],[Union7],[Union8],[Union9],[Union10],[Union11],[Union12],[Photo],[isDel])
VALUES
(@AID,@DeptID,@EmpID,@Title,@Salary,@Job
,@EnrollNo,@Accountno,@NationalID,@JobID
,@ProvinceID,@ShiftID,@CallName,@KhName,@KLName,@KName,@ELName,@EName
,@Gender,@Status,@PersonalID,@NSSF,@Education
,@WorkBook,@CardNo,@Address,@PrePaid,@Paid
,@IsPermanent,@IsContract,@IsResign,@ResignType,@ResignReason,@ResignDate,@BirthDate,@CreateDate
,@Union1,@Union2,@Union3,@Union4,@Union5,@Union6,@Union7,@Union8,@Union9,@Union10,@Union11,@Union12,@Photo,@isDel)
";
SqlParameter[] ps =
{
new SqlParameter("@AID", SqlDbType.Decimal)
{
Value = SqlHelper.ToDbValue(emp.AID)
},
new SqlParameter("DeptID", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.DeptID)
},
new SqlParameter("@Title", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.Title)
},
new SqlParameter("@Salary", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.Salary)
},
new SqlParameter("@Job", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.Job)
},
new SqlParameter("@EmpID", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.EmpID)
},
new SqlParameter("@EnrollNo", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.EnrollNo)
},
new SqlParameter("@Accountno", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Accountno)
},
new SqlParameter("@NationalID", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.NationalID)
},
new SqlParameter("@JobID", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.JobID)
},
new SqlParameter("@ProvinceID", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.ProvinceID)
},
new SqlParameter("@ShiftID", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.ShiftID)
},
new SqlParameter("@CallName", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.CallName)
},
new SqlParameter("@KhName", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.KhName)
},
new SqlParameter("@KLName", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.KLName)
},
new SqlParameter("@KName", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.KName)
},
new SqlParameter("@ELName", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.ELName)
},
new SqlParameter("@EName", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.EName)
},
new SqlParameter("@Gender", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.Gender)
},
new SqlParameter("@Status", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.Status)
},
new SqlParameter("@PersonalID", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.PersonalID)
},
new SqlParameter("@NSSF", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.NSSF)
},
new SqlParameter("@Education", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.Education)
},
new SqlParameter("@WorkBook", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.WorkBook)
},
new SqlParameter("@CardNo", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.CardNo)
},
new SqlParameter("@Address", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.Address)
},
new SqlParameter("@PrePaid", SqlDbType.Bit)
{
Value = SqlHelper.ToDbValue(emp.PrePaid)
},
new SqlParameter("@Paid", SqlDbType.Bit)
{
Value = SqlHelper.ToDbValue(emp.Paid)
},
new SqlParameter("@IsPermanent", SqlDbType.Bit)
{
Value = SqlHelper.ToDbValue(emp.IsPermanent)
},
new SqlParameter("@IsContract", SqlDbType.Bit)
{
Value = SqlHelper.ToDbValue(emp.IsContract)
},
new SqlParameter("@IsResign", SqlDbType.Bit)
{
Value = SqlHelper.ToDbValue(emp.IsResign)
},
new SqlParameter("@ResignType", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.ResignType)
},
new SqlParameter("@ResignReason", SqlDbType.NVarChar)
{
Value = SqlHelper.ToDbValue(emp.ResignReason)
},
new SqlParameter("@ResignDate", SqlDbType.Date)
{
Value = SqlHelper.ToDbValue(emp.ResignDate)
},
new SqlParameter("@BirthDate", SqlDbType.Date)
{
Value = SqlHelper.ToDbValue(emp.BirthDate)
},
new SqlParameter("@CreateDate", SqlDbType.Date)
{
Value = SqlHelper.ToDbValue(emp.CreateDate)
},
new SqlParameter("@Union1", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union1)
},
new SqlParameter("@Union2", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union2)
},
new SqlParameter("@Union3", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union3)
},
new SqlParameter("@Union4", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union4)
},
new SqlParameter("@Union5", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union5)
},
new SqlParameter("@Union6", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union6)
},
new SqlParameter("@Union7", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union7)
},
new SqlParameter("@Union8", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union8)
},
new SqlParameter("@Union9", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union9)
},
new SqlParameter("@Union10", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union10)
},
new SqlParameter("@Union11", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union11)
},
new SqlParameter("@Union12", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.Union12)
},
new SqlParameter("@Photo", SqlDbType.Image)
{
Value = SqlHelper.ToDbValue(emp.Photo)
},
new SqlParameter("@isDel", SqlDbType.Int)
{
Value = SqlHelper.ToDbValue(emp.isDel)
}
};
return(SqlHelper.ExecuteNonQuery(sql, ps));
}
/// <summary>
/// 加载行数据到对象--集合
/// </summary>
/// <param name="dr"></param>
/// <param name="classes"></param>
public void LoadDataToList1(DataRow dr, MODEL.HR_Employee emp)
{
emp.Guid = (Guid)(SqlHelper.FromDbValue(dr["Guid"]));
emp.AID = Convert.ToDecimal(SqlHelper.FromDbValue(dr["AID"]));
emp.Accountno = (int?)(SqlHelper.FromDbValue(dr["Accountno"]));
emp.EmpID = (string)SqlHelper.FromDbValue(dr["EmpID"]);
emp.Title = (string)SqlHelper.FromDbValue(dr["Title"]);
emp.Job = (string)SqlHelper.FromDbValue(dr["Job"]);
emp.Salary = (string)SqlHelper.FromDbValue(dr["Salary"]);
emp.ELName = (string)SqlHelper.FromDbValue(dr["ELName"]);
emp.EName = (string)SqlHelper.FromDbValue(dr["EName"]);
emp.KLName = (string)SqlHelper.FromDbValue(dr["KLName"]);
emp.KName = (string)SqlHelper.FromDbValue(dr["KName"]);
emp.EnrollNo = Convert.ToSingle(SqlHelper.FromDbValue(dr["EnrollNo"]));
// emp.EnrollNo = (float)SqlHelper.FromDbValue(dr["EnrollNo"]);
emp.CallName = (string)SqlHelper.FromDbValue(dr["CallName"]);
emp.KhName = (string)SqlHelper.FromDbValue(dr["KhName"]);
emp.HireDate = (DateTime?)SqlHelper.FromDbValue(dr["HireDate"]);
emp.BirthDate = (DateTime?)SqlHelper.FromDbValue(dr["BirthDate"]);
emp.DeptID = (int?)(SqlHelper.FromDbValue(dr["DeptID"]));
emp.JobID = (int?)(SqlHelper.FromDbValue(dr["JobID"]));
emp.NationalID = (int?)(SqlHelper.FromDbValue(dr["NationalID"]));
emp.ProvinceID = (int?)(SqlHelper.FromDbValue(dr["ProvinceID"]));
emp.ShiftID = (int?)(SqlHelper.FromDbValue(dr["ShiftID"]));
emp.Union1 = (int?)(SqlHelper.FromDbValue(dr["Union1"]));
emp.Union2 = (int?)(SqlHelper.FromDbValue(dr["Union2"]));
emp.Union3 = (int?)(SqlHelper.FromDbValue(dr["Union3"]));
emp.Union4 = (int?)(SqlHelper.FromDbValue(dr["Union4"]));
emp.Union5 = (int?)(SqlHelper.FromDbValue(dr["Union5"]));
emp.Union6 = (int?)(SqlHelper.FromDbValue(dr["Union6"]));
emp.Union7 = (int?)(SqlHelper.FromDbValue(dr["Union7"]));
emp.Union8 = (int?)(SqlHelper.FromDbValue(dr["Union8"]));
emp.Union9 = (int?)(SqlHelper.FromDbValue(dr["Union9"]));
emp.Union10 = (int?)(SqlHelper.FromDbValue(dr["Union10"]));
emp.Union11 = (int?)(SqlHelper.FromDbValue(dr["Union11"]));
emp.Union12 = (int?)(SqlHelper.FromDbValue(dr["Union12"]));
emp.isDel = (int?)(SqlHelper.FromDbValue(dr["isDel"]));
emp.PrePaid = (bool?)(SqlHelper.FromDbValue(dr["PrePaid"]));
emp.Paid = (bool?)(SqlHelper.FromDbValue(dr["Paid"]));
emp.IsContract = (bool?)(SqlHelper.FromDbValue(dr["IsContract"]));
emp.IsPermanent = (bool?)(SqlHelper.FromDbValue(dr["IsPermanent"]));
emp.IsResign = (bool?)(SqlHelper.FromDbValue(dr["IsResign"]));
emp.Address = (string)SqlHelper.FromDbValue(dr["Address"]);
emp.Gender = (string)SqlHelper.FromDbValue(dr["Gender"]);
emp.Status = (string)SqlHelper.FromDbValue(dr["Status"]);
emp.NSSF = (string)SqlHelper.FromDbValue(dr["NSSF"]);
emp.WorkBook = (string)SqlHelper.FromDbValue(dr["WorkBook"]);
emp.Education = (string)SqlHelper.FromDbValue(dr["Education"]);
emp.CardNo = (string)SqlHelper.FromDbValue(dr["CardNo"]);
emp.PersonalID = (string)SqlHelper.FromDbValue(dr["PersonalID"]);
emp.ResignType = (string)SqlHelper.FromDbValue(dr["ResignType"]);
emp.ResignDate = (DateTime?)SqlHelper.FromDbValue(dr["ResignDate"]);
emp.ResignReason = (string)SqlHelper.FromDbValue(dr["ResignReason"]);
emp.CreateDate = (DateTime?)SqlHelper.FromDbValue(dr["CreateDate"]);
emp.KeepFrom = (DateTime?)SqlHelper.FromDbValue(dr["KeepFrom"]);
emp.BackWork = (DateTime?)SqlHelper.FromDbValue(dr["BackWork"]);
emp.Photo = (byte[] )SqlHelper.FromDbValue(dr["Photo"]);
// emp.Pic= ()SqlHelper.FromDbValue(dr["Pic"]);
}
/// <summary>
///
/// </summary>
/// <param name=></param>
/// <returns></returns>
public List <MODEL.HR_Employee> SeeHREmployee(string mempid, string menorllno, bool mchkfaid, string mfaid, bool mchktaid, string mtaid, string mdel)
{
string sql = @"
select * from HR_Employee
";
List <string> listWhere = new List <string>();
List <SqlParameter> listParameters = new List <SqlParameter>();
if (mempid != "")
{
listWhere.Add(" EmpID like @EmpID ");
listParameters.Add(new SqlParameter("@EmpID", mempid + "%"));
}
if (menorllno != "")
{
listWhere.Add(" EnrollNo=@EnrollNo ");
listParameters.Add(new SqlParameter("@EnrollNo", menorllno));
}
if (mchkfaid == true)
{
if (mchktaid == true)
{
listWhere.Add(" AID >=@FAID and AID<=@TAID ");
listParameters.Add(new SqlParameter("@FAID", mfaid));
listParameters.Add(new SqlParameter("@TAID", mtaid));
}
else
{
listWhere.Add(" AID=@FAID ");
listParameters.Add(new SqlParameter("@FAID", mfaid));
}
}
if (mdel != "")
{
listWhere.Add(" isDel<=@isDel ");
listParameters.Add(new SqlParameter("@isDel", mdel));
}
if (listWhere.Count > 0)
{
string sqlWhere = string.Join(" and ", listWhere.ToArray());
sql = sql + " where " + sqlWhere + " order by AID,EmpID ";
}
DataTable dt = SqlHelper.ExcuteTable(sql, listParameters.ToArray());
List <MODEL.HR_Employee> lists = null;
if (dt.Rows.Count > 0)
{
lists = new List <MODEL.HR_Employee>();
foreach (DataRow row in dt.Rows)
{
MODEL.HR_Employee c = new MODEL.HR_Employee();
LoadDataToList1(row, c);
lists.Add(c);
}
}
return(lists);
}
private void btnUpdate_Click(object sender, EventArgs e)
{
if (addoredit == "EDIT")
{
upEmp = this.dgvEmployee.SelectedRows[0].DataBoundItem as MODEL.HR_Employee;
}
else if (addoredit == "ADD")
{
upEmp = new MODEL.HR_Employee();
}
else
{
MessageBox.Show("出現其他狀況,請找IT");
}
// MODEL.HR_Employee upEmp = this.dgvEmployee.SelectedRows[0].DataBoundItem as MODEL.HR_Employee;
// upEmp.isDel = this.cboIsDel.SelectedIndex;
// hrem.UpdateHREmployee(upEmp);
if (MessageBox.Show("OK please enter <確定>", "Employee", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
{
////
//// MODEL.HR_Employee upEmp = this.dgvEmployee.SelectedRows[0].DataBoundItem as MODEL.HR_Employee;
//Dept
//DeptID 舊的欄位先不用
if (cbAID.Checked == true)
{
if (!myCommon.IsWholeNumber((txtAID2.Text.Trim())))
{
MessageBox.Show("請選擇右邊部門代號, Please choice in right Dept AID！", "提交提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
upEmp.AID = Convert.ToDecimal(txtAID2.Text.Trim());
}
else
{
upEmp.AID = Convert.ToDecimal(txtAID1.Text.Trim());
}
//Title
upEmp.EmpID = txtEmpID.Text;
upEmp.Title = cboTitle.Text;
upEmp.Salary = cboSalary.Text;
upEmp.Job = cboJob.Text;
//Name
upEmp.KhName = txtKhName.Text;
upEmp.CallName = txtCallName.Text;
upEmp.KLName = txtKLName.Text;
upEmp.KName = txtKName.Text;
upEmp.ELName = txtELName.Text;
upEmp.EName = txtEName.Text;
//
if (!myCommon.IsWholeNumber((txtEnrollNo.Text.Trim())))
{
MessageBox.Show("卡號请输入数字,EnrollNo please type number！", "提交提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
upEmp.EnrollNo = Convert.ToInt32(txtEnrollNo.Text.Trim());
upEmp.PersonalID = txtPersonalID.Text.Trim();
upEmp.NSSF = txtNSSF.Text.Trim();
upEmp.WorkBook = txtWorkBook.Text.Trim();
upEmp.NationalID = this.cboNationalID.SelectedIndex;
upEmp.ShiftID = this.cboShiftID.SelectedIndex;
upEmp.Education = cboEducation.Text;
upEmp.Status = cboStatus.Text;
upEmp.ProvinceID = this.cboProvinceID.SelectedIndex;
upEmp.Address = txtAddress.Text;
upEmp.CardNo = txtCardNo.Text;
//accountno 現在不用
//性別Gender
if (rdFemale.Checked == true)
{
upEmp.Gender = "0";
}
else if (rdMale.Checked == true)
{
upEmp.Gender = "1";
}
else
{
upEmp.Gender = "-1 ";
}
//日期 Date
if (dtpBirthDate.CustomFormat == " " || dtpBirthDate.Value == null)
{
upEmp.BirthDate = null;
}
else
{
upEmp.BirthDate = dtpBirthDate.Value;
}
if (dtpHireDate.CustomFormat == " " || dtpHireDate.Value == null)
{
upEmp.HireDate = null;
}
else
{
upEmp.HireDate = dtpHireDate.Value;
}
if (dtpCreateDate.CustomFormat == " " || dtpCreateDate.Value == null)
{
upEmp.CreateDate = null;
}
else
{
upEmp.CreateDate = dtpCreateDate.Value;
}
//Keep
if (dtpKeepFrom.CustomFormat == " " || dtpKeepFrom.Value == null)
{
upEmp.KeepFrom = null;
}
else
{
upEmp.KeepFrom = dtpKeepFrom.Value;
}
if (dtpBackWork.CustomFormat == " " || dtpBackWork.Value == null)
{
upEmp.BackWork = null;
}
else
{
upEmp.BackWork = dtpBackWork.Value;
}
upEmp.isDel = this.cboIsDel.SelectedIndex;
//bool
upEmp.PrePaid = cbPrePaid.Checked;
upEmp.Paid = cbPaid.Checked;
upEmp.IsPermanent = cbIsPermanent.Checked;
upEmp.IsContract = cbIsContract.Checked;
upEmp.IsResign = cbIsResign.Checked;
// 離職Resign
upEmp.ResignType = txtResignType.Text;
upEmp.ResignReason = txtResignReason.Text;
//公會 Union
upEmp.Union1 = Convert.ToInt16(cbUnion1.Checked);
upEmp.Union2 = Convert.ToInt16(cbUnion2.Checked);
upEmp.Union3 = Convert.ToInt16(cbUnion3.Checked);
upEmp.Union4 = Convert.ToInt16(cbUnion4.Checked);
upEmp.Union5 = Convert.ToInt16(cbUnion5.Checked);
upEmp.Union6 = Convert.ToInt16(cbUnion6.Checked);
upEmp.Union7 = Convert.ToInt16(cbUnion7.Checked);
upEmp.Union8 = Convert.ToInt16(cbUnion8.Checked);
upEmp.Union9 = Convert.ToInt16(cbUnion9.Checked);
upEmp.Union10 = Convert.ToInt16(cbUnion10.Checked);
upEmp.Union11 = Convert.ToInt16(cbUnion11.Checked);
upEmp.Union12 = Convert.ToInt16(cbUnion12.Checked);
//
if (txtPhotopath.Text != null && txtPhotopath.Text != "")
{
byte[] photo_byte = null;
// 把图片转换为二进制保存
// 创建FileStream对象
FileStream fs = new FileStream(txtPhotopath.Text, FileMode.Open, FileAccess.Read);
// 声明Byte数组
photo_byte = new byte[fs.Length];
// 读取数据
fs.Read(photo_byte, 0, photo_byte.Length);
fs.Close();
upEmp.Photo = photo_byte;
}
else
{
//修改原來的不用改
if (addoredit == "ADD")
{
upEmp.Photo = null;
}
else
{
upEmp.Photo = mphoto;
}
}
if (addoredit == "EDIT")
{
upEmp.Guid = new Guid(txtGuid.Text);
hrem.UpdateHREmployee(upEmp);
}
else if (addoredit == "ADD")
{
hrem.AddHREmployee(upEmp);
}
else
{
MessageBox.Show("出現其他狀況,請找IT");
}
//
}
else
{
MessageBox.Show("no");
}
}
/// <summary>
/// 修改操作，得到对象的属性值填充在控件中
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void tsmiUpdate_Click(object sender, EventArgs e)
{
txtPhotopath.Text = "";
this.gbEdit.Text = "修改ADD";
addoredit = "EDIT";
cbAID.Checked = false;
if (this.dgvEmployee.SelectedRows.Count == 0)
{
return;
}
MODEL.HR_Employee upEmp = this.dgvEmployee.SelectedRows[0].DataBoundItem as MODEL.HR_Employee;
txtGuid.Text = upEmp.Guid.ToString().Trim();
//Detp
txtAID1.Text = upEmp.AID.ToString();
cboAID1Unit.SelectedValue = upEmp.AID;
cboAID1UnitName.SelectedValue = upEmp.AID;
// cboJobID.SelectedValue = upEmp.JobID;
// int jobid = Convert.ToInt16(upEmp.JobID);
//Title
txtEmpID.Text = upEmp.EmpID.Trim();
cboTitle.Text = upEmp.Title;
cboSalary.Text = upEmp.Salary;
cboJob.Text = upEmp.Job;
//Name
txtKhName.Text = upEmp.KhName;
txtCallName.Text = upEmp.CallName;
txtELName.Text = upEmp.ELName;
txtEName.Text = upEmp.EName;
txtKLName.Text = upEmp.KLName;
txtKName.Text = upEmp.KName;
txtEnrollNo.Text = upEmp.EnrollNo.ToString();
txtPersonalID.Text = upEmp.PersonalID;
txtNSSF.Text = upEmp.NSSF;
txtWorkBook.Text = upEmp.WorkBook;
cboNationalID.SelectedIndex = (int)upEmp.NationalID;
cboShiftID.SelectedIndex = (int)upEmp.ShiftID;
cboEducation.Text = upEmp.Education;
cboStatus.Text = upEmp.Status;
cboProvinceID.SelectedIndex = (int)upEmp.ProvinceID;
txtAddress.Text = upEmp.Address;
txtCardNo.Text = upEmp.CardNo.Trim();
// txtAccountno 這個現在不用
// txtGender.Text = upEmp.Gender.ToString();
if (upEmp.Gender.Trim() == "0")
{
rdFemale.Checked = true;
}
else if (upEmp.Gender.Trim() == "1")
{
rdMale.Checked = true;
}
else
{
}
int mdel = Convert.ToInt16(upEmp.isDel);
this.cboIsDel.SelectedIndex = mdel;
DateTime mdate = Convert.ToDateTime("1800-01-01");
if (upEmp.BirthDate > mdate)
{
dtpBirthDate.Format = DateTimePickerFormat.Custom;
dtpBirthDate.CustomFormat = "yyyy-MM-dd";
dtpBirthDate.Value = Convert.ToDateTime(upEmp.BirthDate);
}
else
{
// 日期格式初始為空設定
dtpBirthDate.Format = DateTimePickerFormat.Custom;
dtpBirthDate.CustomFormat = " ";//这里是一个空格，否则无法生效的
}
if (upEmp.HireDate > mdate)
{
dtpHireDate.Format = DateTimePickerFormat.Custom;
dtpHireDate.CustomFormat = "yyyy-MM-dd";
dtpHireDate.Value = Convert.ToDateTime(upEmp.HireDate);
}
else
{
// 日期格式初始為空設定
dtpHireDate.Format = DateTimePickerFormat.Custom;
dtpHireDate.CustomFormat = " ";//这里是一个空格，否则无法生效的
}
if (upEmp.ResignDate > mdate)
{
dtpResignDate.Format = DateTimePickerFormat.Custom;
dtpResignDate.CustomFormat = "yyyy-MM-dd";
dtpResignDate.Value = Convert.ToDateTime(upEmp.ResignDate);
}
else
{
// 日期格式初始為空設定
dtpResignDate.Format = DateTimePickerFormat.Custom;
dtpResignDate.CustomFormat = " ";//这里是一个空格，否则无法生效的
}
if (upEmp.KeepFrom > mdate)
{
dtpKeepFrom.Format = DateTimePickerFormat.Custom;
dtpKeepFrom.CustomFormat = "yyyy-MM-dd";
dtpKeepFrom.Value = Convert.ToDateTime(upEmp.KeepFrom);
}
else
{
// 日期格式初始為空設定
dtpKeepFrom.Format = DateTimePickerFormat.Custom;
dtpKeepFrom.CustomFormat = " ";//这里是一个空格，否则无法生效的
}
if (upEmp.BackWork > mdate)
{
dtpBackWork.Format = DateTimePickerFormat.Custom;
dtpBackWork.CustomFormat = "yyyy-MM-dd";
dtpBackWork.Value = Convert.ToDateTime(upEmp.BackWork);
}
else
{
// 日期格式初始為空設定
dtpBackWork.Format = DateTimePickerFormat.Custom;
dtpBackWork.CustomFormat = " ";//这里是一个空格，否则无法生效的
}
// bool
cbPrePaid.Checked = Convert.ToBoolean(upEmp.PrePaid);
cbPaid.Checked = Convert.ToBoolean(upEmp.Paid);
cbIsContract.Checked = Convert.ToBoolean(upEmp.IsContract);
cbIsPermanent.Checked = Convert.ToBoolean(upEmp.IsPermanent);
cbIsResign.Checked = Convert.ToBoolean(upEmp.IsResign);
//Union
cbUnion1.Checked = Convert.ToBoolean(upEmp.Union1);
cbUnion2.Checked = Convert.ToBoolean(upEmp.Union2);
cbUnion3.Checked = Convert.ToBoolean(upEmp.Union3);
cbUnion4.Checked = Convert.ToBoolean(upEmp.Union4);
cbUnion5.Checked = Convert.ToBoolean(upEmp.Union5);
cbUnion6.Checked = Convert.ToBoolean(upEmp.Union6);
cbUnion7.Checked = Convert.ToBoolean(upEmp.Union7);
cbUnion8.Checked = Convert.ToBoolean(upEmp.Union8);
cbUnion9.Checked = Convert.ToBoolean(upEmp.Union9);
cbUnion10.Checked = Convert.ToBoolean(upEmp.Union10);
cbUnion11.Checked = Convert.ToBoolean(upEmp.Union11);
cbUnion12.Checked = Convert.ToBoolean(upEmp.Union12);
mphoto = upEmp.Photo;
if (upEmp.Photo != null)
{
//byte 轉換成image 顯示圖片
Image photo = null;
using (MemoryStream ms = new MemoryStream(upEmp.Photo))
{
ms.Write(upEmp.Photo, 0, upEmp.Photo.Length);
photo = Image.FromStream(ms, true);
}
pbPhoto.Image = photo;
}
else
{
pbPhoto.Image = null;
}
this.gbEdit.Visible = true;
cbAID.Enabled = true;
cbAID.Checked = false;
}
private void loadExcel()
{
if (txtfilename.Text.Length <= 0)
{
MessageBox.Show("请选择文件！");
Cursor = Cursors.Default;
return;
}
if (comsheetname.SelectedIndex == -1)
{
MessageBox.Show("请选择表名！");
comsheetname.DroppedDown = true;
Cursor = Cursors.Default;
return;
}
string sheetname = comsheetname.SelectedItem.ToString();
int headno = 0;
hremp1 = NPOIexcel.ExcelReadEmp(filename, sheetname, headno);
// hremp = NPOIexcel.ExcelReadEmpTable(filename, sheetname, headno);
if (hremp1 != null)
{
this.dgvEmployee.DataSource = hremp1;
// this.dataGridView1.DataSource = hremp;
}
for (int i = 0; i < hremp1.Count; i++)
{
MODEL.HR_Employee emp = new MODEL.HR_Employee();
emp.AID = hremp1[i].AID;
emp.DeptID = hremp1[i].DeptID;
emp.EmpID = hremp1[i].EmpID;
emp.EnrollNo = hremp1[i].EnrollNo;
emp.Accountno = hremp1[i].Accountno;
emp.NationalID = hremp1[i].NationalID;
emp.JobID = hremp1[i].JobID;
emp.ProvinceID = hremp1[i].ProvinceID;
emp.ShiftID = hremp1[i].ShiftID;
emp.CallName = hremp1[i].CallName;
emp.KhName = hremp1[i].KhName;
emp.Gender = hremp1[i].Gender;
emp.Status = hremp1[i].Status;
emp.PersonalID = hremp1[i].PersonalID;
emp.NSSF = hremp1[i].NSSF;
emp.Education = hremp1[i].Education;
emp.WorkBook = hremp1[i].WorkBook;
emp.CardNo = hremp1[i].CardNo;
emp.Address = hremp1[i].Address;
emp.IsPermanent = hremp1[i].IsPermanent;
emp.IsContract = hremp1[i].IsContract;
emp.IsResign = hremp1[i].IsResign;
emp.ResignType = hremp1[i].ResignType;
emp.ResignReason = hremp1[i].ResignReason;
emp.ResignDate = hremp1[i].ResignDate;
emp.BirthDate = hremp1[i].BirthDate;
emp.CreateDate = hremp1[i].CreateDate;
emp.Union1 = hremp1[i].Union1;
emp.Union2 = hremp1[i].Union2;
emp.Union3 = hremp1[i].Union3;
emp.Union4 = hremp1[i].Union4;
emp.Union5 = hremp1[i].Union5;
emp.Union6 = hremp1[i].Union6;
emp.Union7 = hremp1[i].Union7;
emp.Union8 = hremp1[i].Union8;
emp.Union9 = hremp1[i].Union9;
emp.Union10 = hremp1[i].Union10;
emp.Union11 = hremp1[i].Union11;
emp.Union12 = hremp1[i].Union12;
emp.isDel = 0; //false 在職
// empm.AddHREmployee(emp);
}
}
private void btnEmployee_Click(object sender, EventArgs e)
{
List <MODEL.Employee> emplist = new List <MODEL.Employee>();
float menrollno = Convert.ToSingle(txtEnrollNo.Text);
emplist = empm.GetEmployee(menrollno);
// this.dgvEmployee.DataSource = emplist;
// MODEL.HR_Employee hremp = new MODEL.HR_Employee();
// MODEL.HR_Employee hremp1 = new MODEL.HR_Employee();
for (int i = 0; i < emplist.Count; i++)
{
test.Clear();
test.AppendText("人員資料" + "\r\n" + "轉換" + "\r\n" + (i + 1).ToString() + "/ " + emplist.Count.ToString());
MODEL.HR_Employee hremp = new MODEL.HR_Employee();
// ,[X]
// ,[Y]
// ,[Photo]
hremp.Guid = System.Guid.NewGuid();
hremp.DeptID = emplist[i].DeptID;
int mdept = Convert.ToInt16(emplist[i].DeptID);
double?maid = empm.GetAID(mdept);
hremp.AID = Convert.ToDecimal(maid);
hremp.EmpID = emplist[i].EmpID; //工號
hremp.EnrollNo = emplist[i].EnrollNo; //指紋機編號
hremp.Accountno = emplist[i].Accountno;
hremp.NationalID = emplist[i].NationalID;
hremp.JobID = emplist[i].JobID;
hremp.ProvinceID = emplist[i].ProvinceID;
hremp.ShiftID = emplist[i].ShiftID;
hremp.CallName = emplist[i].CallName; //別名
hremp.KhName = emplist[i].KhName; //柬埔寨名
hremp.Gender = emplist[i].Gender;
hremp.Status = emplist[i].Status;
hremp.PersonalID = emplist[i].PersonalID;
hremp.NSSF = emplist[i].NSSF;
hremp.Education = emplist[i].Education;
hremp.WorkBook = emplist[i].WorkBook;
hremp.CardNo = emplist[i].CardNo;
hremp.Address = emplist[i].Address;
hremp.IsPermanent = emplist[i].IsPermanent;
hremp.IsContract = emplist[i].IsContract;
hremp.IsResign = emplist[i].IsResign;
hremp.ResignType = emplist[i].ResignType;
hremp.ResignReason = emplist[i].ResignReason;
hremp.ResignDate = emplist[i].ResignDate;
hremp.BirthDate = emplist[i].BirthDate;
hremp.CreateDate = emplist[i].CreateDate;
hremp.Union1 = emplist[i].Union1;
hremp.Union2 = emplist[i].Union2;
hremp.Union3 = emplist[i].Union3;
hremp.Union4 = emplist[i].Union4;
hremp.Union5 = emplist[i].Union5;
hremp.Union6 = emplist[i].Union6;
hremp.Union7 = emplist[i].Union7;
hremp.Union8 = emplist[i].Union8;
hremp.Union9 = emplist[i].Union9;
hremp.Union10 = emplist[i].Union10;
hremp.Union11 = emplist[i].Union11;
hremp.Union12 = emplist[i].Union12;
// empm.TransEmp(hremp);
hremplist.Add(hremp);
}
this.dgvEmployee.DataSource = null;
this.dgvEmployee.DataSource = hremplist;
}
