public Contract GetContractByID(int contractID)
{
using (var connection = new OleDbConnection(ConfigManagement.AccessConStr)) {
string sqlText = string.Format(@"select
ContractID,
PeopleID,
ContractrType,
IsFirstContract,
ContractNum,
StartDate,
EndDate,
ProbationEndDate
from {0} where ContractID=@ContractID", _ContractTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("ContractID", contractID);
var reader = new OLESqlHelper().GetReader(sqlText, connection, paras);
if (reader.Read()) {
int i = 0;
var contract = new Contract();
contract.ContractID = reader.GetInt32(i++);
contract.PeopleID = reader.GetInt32(i++);
contract.ContractrType = (int)reader.GetInt32(i++);
contract.IsFirstContract = (bool)reader.GetBoolean(i++);
contract.ContractNum = reader.GetString(i++);
contract.StartDate = reader.IsDBNull(i) ? new Nullable<DateTime>() : reader.GetDateTime(i++);
contract.EndDate = reader.IsDBNull(i) ? new Nullable<DateTime>() : reader.GetDateTime(i++);
contract.ProbationEndDate = reader.IsDBNull(i) ? new Nullable<DateTime>() : reader.GetDateTime(i++);
return contract;
} else {
return null;
}
}
}
public InsuranceFund GetInsuranceFundByID(int InsuranceFundID)
{
using (var connection = new OleDbConnection(ConfigManagement.AccessConStr)) {
string sqlText = string.Format(@"select
InsuranceFundID,
PeopleID,
InsuranceFundType,
Number,
PaymentAddress,
PaymentStartDate,
PaymentEndDate,
BasicNum
from {0} where InsuranceFundID=@InsuranceFundID", _InsuranceFundTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("InsuranceFundID", InsuranceFundID);
var reader = new OLESqlHelper().GetReader(sqlText, connection, paras);
if (reader.Read()) {
int i = 0;
var InsuranceFund = new InsuranceFund();
InsuranceFund.InsuranceFundID = reader.GetInt32(i++);
InsuranceFund.PeopleID = reader.GetInt32(i++);
InsuranceFund.InsuranceFundType = (int)reader.GetInt32(i++);
InsuranceFund.Number = reader.GetString(i++);
InsuranceFund.PaymentAddress = reader.GetString(i++);
InsuranceFund.PaymentStartDate = reader.IsDBNull(i) ? new Nullable<DateTime>() : reader.GetDateTime(i++);
InsuranceFund.PaymentEndDate = reader.IsDBNull(i) ? new Nullable<DateTime>() : reader.GetDateTime(i++);
InsuranceFund.BasicNum = reader.GetFloat(i++);
return InsuranceFund;
} else {
return null;
}
}
}
public RewardRecord GetRewardRecordByID(int recordID)
{
using (var connection = new OleDbConnection(ConfigManagement.AccessConStr)) {
string sqlText = string.Format(@"select
RecordID,
PeopleID,
RewardDate,
RewardContent
from {0} where RecordID=@RecordID", _RewardRecordTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("RecordID", recordID);
var reader = new OLESqlHelper().GetReader(sqlText, connection, paras);
if (reader.Read()) {
int i = 0;
var RewardRecord = new RewardRecord();
RewardRecord.RecordID = reader.GetInt32(i++);
RewardRecord.PeopleID = reader.GetInt32(i++);
RewardRecord.RewardDate = reader.IsDBNull(i) ? new Nullable<DateTime>() : reader.GetDateTime(i++);
RewardRecord.RewardContent = reader.GetString(i++);
return RewardRecord;
} else {
return null;
}
}
}
public List<RewardRecord> GetRewardRecordList(int peopleID)
{
string sqlText = string.Format(@"select
RecordID,
RewardDate,
RewardContent
from {0}
where PeopleID=@PeopleID"
, _RewardRecordTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("PeopleID", peopleID);
var table = new OLESqlHelper().GetTable(sqlText, paras);
var rewardRecordList = new List<RewardRecord>();
foreach (DataRow row in table.Rows) {
var rewardRecord = new RewardRecord() {
RecordID = (int)row["RecordID"],
RewardDate = (DateTime?)row["RewardDate"],
RewardContent = row["RewardContent"].ToString(),
};
rewardRecordList.Add(rewardRecord);
}
return rewardRecordList;
}
public PeopleFamily GetPeopleFamilyByID(int familyPeopleID)
{
using (var connection = new OleDbConnection(ConfigManagement.AccessConStr)) {
string sqlText = string.Format(@"select
PeopleFamilyID,
PeopleID,
Relation,
FamilyPeopleName,
WorkUnit,
Title,
PhoneNum,
Address
from {0} where PeopleFamilyID=@PeopleFamilyID", _peopleFamilyTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("PeopleFamilyID", familyPeopleID);
var reader = new OLESqlHelper().GetReader(sqlText, connection, paras);
if (reader.Read()) {
int i = 0;
var peopleFamily = new PeopleFamily();
peopleFamily.PeopleFamilyID = reader.GetInt32(i++);
peopleFamily.PeopleID = reader.GetInt32(i++);
peopleFamily.Relation = reader.GetString(i++);
peopleFamily.FamilyPeopleName = reader.GetString(i++);
peopleFamily.WorkUnit = reader.GetString(i++);
peopleFamily.Title = reader.GetString(i++);
peopleFamily.PhoneNum = reader.GetString(i++);
peopleFamily.Address = reader.GetString(i++);
return peopleFamily;
} else {
return null;
}
}
}
public List<PeopleFamily> GetPeopleFamilyList(int peopleID)
{
string sqlText = string.Format(@"select
FamilyPeopleID,
Relation,
FamilyPeopleName,
WorkUnit,
Title,
PhoneNum,
Address
from {0}
where PeopleID=@PeopleID"
, _peopleFamilyTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("PeopleID", peopleID);
var table = new OLESqlHelper().GetTable(sqlText, paras);
var peopleFamilyList = new List<PeopleFamily>();
foreach (DataRow row in table.Rows) {
var peopleFamily = new PeopleFamily() {
PeopleFamilyID = (int)row["FamilyPeopleID"],
Relation = row["Relation"].ToString(),
FamilyPeopleName = row["FamilyPeopleName"].ToString(),
WorkUnit = row["WorkUnit"].ToString(),
Title = row["Title"].ToString(),
PhoneNum = row["PhoneNum"].ToString(),
Address = row["Address"].ToString()
};
peopleFamilyList.Add(peopleFamily);
}
return peopleFamilyList;
}
public List<PeoplePic> GetPeoplePicList(int peopleID)
{
string sqlText = string.Format(@"select
PicID,
PicTitle,
PicPath
from {0}
where PeopleID=@PeopleID"
, _PeoplePicTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("PeopleID", peopleID);
var table = new OLESqlHelper().GetTable(sqlText, paras);
var PeoplePicList = new List<PeoplePic>();
foreach (DataRow row in table.Rows) {
var PeoplePic = new PeoplePic() {
PicID = (int)row["PicID"],
PicTitle = row["PicTitle"].ToString(),
PicPath = row["PicPath"].ToString(),
};
PeoplePicList.Add(PeoplePic);
}
return PeoplePicList;
}
public PeoplePic GetPeoplePicByID(int PicID)
{
using (var connection = new OleDbConnection(ConfigManagement.AccessConStr)) {
string sqlText = string.Format(@"select
PicID,
PeopleID,
PicTitle,
PicPath
from {0} where PicID=@PicID", _PeoplePicTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("PicID", PicID);
var reader = new OLESqlHelper().GetReader(sqlText, connection, paras);
if (reader.Read()) {
int i = 0;
var PeoplePic = new PeoplePic();
PeoplePic.PicID = reader.GetInt32(i++);
PeoplePic.PeopleID = reader.GetInt32(i++);
PeoplePic.PicTitle = reader.GetString(i++);
PeoplePic.PicPath = reader.GetString(i++);
return PeoplePic;
} else {
return null;
}
}
}
public List<Contract> GetContractList(int peopleID)
{
string sqlText = string.Format(@"select
ContractID,
ContractrType,
IsFirstContract,
ContractNum,
StartDate,
EndDate,
ProbationEndDate
from {0}
where PeopleID=@PeopleID"
, _ContractTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("PeopleID", peopleID);
var table = new OLESqlHelper().GetTable(sqlText, paras);
var contractList = new List<Contract>();
foreach (DataRow row in table.Rows) {
var peopleFamily = new Contract() {
ContractID = (int)row["ContractID"],
ContractrType = (int)row["ContractrType"],
IsFirstContract = (bool)row["IsFirstContract"],
ContractNum = row["ContractNum"].ToString(),
StartDate = (DateTime?)row["StartDate"],
EndDate = (DateTime?)row["EndDate"],
ProbationEndDate = row.IsNull("ProbationEndDate") ? new Nullable<DateTime>() : (DateTime?)row["ProbationEndDate"]
};
contractList.Add(peopleFamily);
}
return contractList;
}
public List<PeopleBasicInfo> GetPeopleList(out int totalCount, string searchKey = "", int pageIndex = 1, int pageSize = 10)
{
searchKey = searchKey.Trim();
string sqlText = string.Format("select count(*) from {0} where (PeopleName like '%{1}%' or WorkerNum like '%{1}%')", _peopleTableName, searchKey);
totalCount = new OLESqlHelper().GetCount(sqlText);
var exceptCount = (pageIndex - 1) * pageSize;
if (exceptCount > 0) {
sqlText = string.Format("select top {0} * from {1} where (PeopleName like '%{3}%' or WorkerNum like '%{3}%') and PeopleID > (select max(PeopleID) from (select top {2} PeopleID from {1} where PeopleName like '%{3}%' or WorkerNum like '%{3}%' order by PeopleID)) order by PeopleID", pageSize, _peopleTableName, exceptCount, searchKey);
} else {
sqlText = string.Format("select top {0} * from {1} where PeopleName like '%{2}%' or WorkerNum like '%{2}%' order by PeopleID", pageSize, _peopleTableName, searchKey);
}
var table = new OLESqlHelper().GetTable(sqlText);
var peopleList = new List<PeopleBasicInfo>();
foreach (DataRow row in table.Rows) {
var people = new PeopleBasicInfo() {
PeopleID = (int)row["PeopleID"],
Gender = row["Gender"].ToString(),
PeopleName = row["PeopleName"].ToString(),
DateOfBirth = DateTime.Parse(row["DateOfBirth"].ToString()),
Department = row["Department"].ToString(),
PhoneNum = row["PhoneNum"].ToString(),
Title = row["Title"].ToString(),
WorkerNum = row["WorkerNum"].ToString(),
TitleState = row["TitleState"].ToString(),
};
peopleList.Add(people);
}
return peopleList;
}
public List<InsuranceFund> GetInsuranceFundList(int peopleID)
{
string sqlText = string.Format(@"select
InsuranceFundID,
InsuranceFundType,
Number,
PaymentAddress,
PaymentStartDate,
PaymentEndDate,
BasicNum
from {0}
where PeopleID=@PeopleID"
, _InsuranceFundTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("PeopleID", peopleID);
var table = new OLESqlHelper().GetTable(sqlText, paras);
var InsuranceFundList = new List<InsuranceFund>();
foreach (DataRow row in table.Rows) {
var peopleFamily = new InsuranceFund() {
InsuranceFundID = (int)row["InsuranceFundID"],
InsuranceFundType = (int)row["InsuranceFundType"],
Number = row["Number"].ToString(),
PaymentAddress = row["PaymentAddress"].ToString(),
PaymentStartDate = (DateTime?)row["PaymentStartDate"],
PaymentEndDate = row.IsNull("PaymentEndDate") ? null : (DateTime?)row["PaymentEndDate"],
BasicNum = (float)row["BasicNum"]
};
InsuranceFundList.Add(peopleFamily);
}
return InsuranceFundList;
}
public bool DeletePeopleByID(int peopleID)
{
string sqlText = string.Format("delete from {0} where PeopleID=@peopleID", _peopleTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("peopleID", peopleID);
var result = new OLESqlHelper().ExecuteNoneQuery(sqlText, paras);
return result;
}
public bool DeleteContractByID(int contractID)
{
string sqlText = string.Format("delete from {0} where contractID=@contractID", _ContractTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("contractID", contractID);
var result = new OLESqlHelper().ExecuteNoneQuery(sqlText, paras);
return result;
}
public bool DeleteTitleWageChangeRecordByID(int TitleWageChangeRecordID)
{
string sqlText = string.Format("delete from {0} where TitleWageChangeRecordID=@TitleWageChangeRecordID", _TitleWageChangeRecordTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("TitleWageChangeRecordID", TitleWageChangeRecordID);
var result = new OLESqlHelper().ExecuteNoneQuery(sqlText, paras);
return result;
}
public bool DeleteInsuranceFundByID(int InsuranceFundID)
{
string sqlText = string.Format("delete from {0} where InsuranceFundID=@InsuranceFundID", _InsuranceFundTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("InsuranceFundID", InsuranceFundID);
var result = new OLESqlHelper().ExecuteNoneQuery(sqlText, paras);
return result;
}
public bool DeleteRewardRecordByID(int recordID)
{
string sqlText = string.Format("delete from {0} where RecordID=@RecordID", _RewardRecordTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("RecordID", recordID);
var result = new OLESqlHelper().ExecuteNoneQuery(sqlText, paras);
return result;
}
public bool IsUserCorrect(string userName, string password)
{
string sqlText = "select count(*) from UserInfo where userName=@userName and password=@password";
OleDbParameter[] paras = new OleDbParameter[2];
paras[0] = new OleDbParameter("userName", userName);
paras[1] = new OleDbParameter("password", password);
var result = new OLESqlHelper().IsExists(sqlText, paras);
return result;
}
public PeopleBasicInfo GetPeopleBasicInfoByID(int peopleID)
{
using (var connection = new OleDbConnection(ConfigManagement.AccessConStr)) {
string sqlText = string.Format(@"select PeopleID, PeopleName,Gender,DateOfBirth,Age,IDCardNum,IsMarried,PoliticalStatus,
Nation,HouseholdRegisterAddress,HouseholdRegisterType,HouseholdRegisterPostCode,CurrentAddress,PhoneNum,CurrentAddressPostCode,
EducationGrade,GraduationSchool,Profession,EmergencyContactPerson,EmergencyContactPersonPhone,EmergencyContactPersonRelation ,WorkerNum from {0} where PeopleID=@peopleID", _peopleTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("peopleID", peopleID);
var reader = new OLESqlHelper().GetReader(sqlText, connection, paras);
if (reader.Read()) {
int i = 0;
var people = new PeopleBasicInfo();
people.PeopleID = reader.GetInt32(i++);
people.PeopleName = reader.GetString(i++);
people.Gender = reader.GetString(i++);
if (!reader.IsDBNull(i)) {
people.DateOfBirth = reader.GetDateTime(i++);
} else {
i++;
}
people.Age = reader.GetInt32(i++);
people.IDCardNum = reader.GetString(i++);
people.IsMarried = reader.GetBoolean(i++);
people.PoliticalStatus = reader.GetString(i++);
people.Nation = reader.GetString(i++);
people.HouseholdRegisterAddress = reader.GetString(i++);
people.HouseholdRegisterType = reader.GetString(i++);
people.HouseholdRegisterPostCode = reader.GetString(i++);
people.CurrentAddress = reader.GetString(i++);
people.PhoneNum = reader.GetString(i++);
people.CurrentAddressPostCode = reader.GetString(i++);
people.EducationGrade = reader.GetString(i++);
people.GraduationSchool = reader.GetString(i++);
people.Profession = reader.GetString(i++);
people.EmergencyContactPerson = reader.GetString(i++);
people.EmergencyContactPersonPhone = reader.GetString(i++);
people.EmergencyContactPersonRelation = reader.GetString(i++);
people.WorkerNum = reader.GetString(i++);
return people;
} else {
return null;
}
}
}
public UserInfo GetUserInfoByName(string userName)
{
var sqlText = "select * from UserInfo where UserName=@userName";
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("userName", userName);
using (var connection = new OleDbConnection(ConfigManagement.AccessConStr)) {
using (var reader = new OLESqlHelper().GetReader(sqlText, connection, paras)) {
reader.Read();
var userInfo = new UserInfo();
userInfo.UserID = reader.GetInt32(0);
userInfo.UserName = reader.GetString(1);
userInfo.Password = reader.GetString(2);
return userInfo;
}
}
}
public TitleWageChangeRecord GetTitleWageChangeRecordByID(int TitleWageChangeRecordID)
{
using (var connection = new OleDbConnection(ConfigManagement.AccessConStr)) {
string sqlText = string.Format(@"select
TitleWageChangeRecordID,
PeopleID,
ChangeDate,
ChangeType,
ContractNum,
BeforeChangeTitle,
BeforeChangeBasicWage,
BeforeChangeAllowance,
BeforeChangeYearAward,
AfterChangeTitle,
AfterChangeBasicWage,
AfterChangeAllowance,
AfterChangeYearAward
from {0} where TitleWageChangeRecordID=@TitleWageChangeRecordID", _TitleWageChangeRecordTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("TitleWageChangeRecordID", TitleWageChangeRecordID);
var reader = new OLESqlHelper().GetReader(sqlText, connection, paras);
if (reader.Read()) {
int i = 0;
var TitleWageChangeRecord = new TitleWageChangeRecord();
TitleWageChangeRecord.TitleWageChangeRecordID = reader.GetInt32(i++);
TitleWageChangeRecord.PeopleID = reader.GetInt32(i++);
TitleWageChangeRecord.ChangeDate = reader.IsDBNull(i) ? new Nullable<DateTime>() : reader.GetDateTime(i++);
TitleWageChangeRecord.ChangeType = reader.GetInt32(i++);
TitleWageChangeRecord.ContractNum = reader.GetString(i++);
TitleWageChangeRecord.BeforeChangeTitle = reader.GetString(i++);
TitleWageChangeRecord.BeforeChangeBasicWage = reader.GetFloat(i++);
TitleWageChangeRecord.BeforeChangeAllowance = reader.GetFloat(i++);
TitleWageChangeRecord.BeforeChangeYearAward = reader.GetFloat(i++);
TitleWageChangeRecord.AfterChangeTitle = reader.GetString(i++);
TitleWageChangeRecord.AfterChangeBasicWage = reader.GetFloat(i++);
TitleWageChangeRecord.AfterChangeAllowance = reader.GetFloat(i++);
TitleWageChangeRecord.AfterChangeYearAward = reader.GetFloat(i++);
return TitleWageChangeRecord;
} else {
return null;
}
}
}
public TrainingTestRecord GetTrainingTestRecordByID(int RecordID)
{
using (var connection = new OleDbConnection(ConfigManagement.AccessConStr)) {
string sqlText = string.Format(@"select
RecordID,
PeopleID,
TrainingDate,
TrainingType,
TrainingContent,
TrainingDepartment,
TestDate,
TestResult,
TestPeople,
Remark
from {0} where RecordID=@RecordID", _TrainingTestRecordTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("RecordID", RecordID);
var reader = new OLESqlHelper().GetReader(sqlText, connection, paras);
if (reader.Read()) {
int i = 0;
var TrainingTestRecord = new TrainingTestRecord();
TrainingTestRecord.RecordID = reader.GetInt32(i++);
TrainingTestRecord.PeopleID = reader.GetInt32(i++);
TrainingTestRecord.TrainingDate = reader.IsDBNull(i) ? new Nullable<DateTime>() : reader.GetDateTime(i++);
TrainingTestRecord.TrainingType = reader.GetString(i++);
TrainingTestRecord.TrainingContent = reader.GetString(i++);
TrainingTestRecord.TrainingDepartment = reader.GetString(i++);
TrainingTestRecord.TestDate = reader.IsDBNull(i) ? new Nullable<DateTime>() : reader.GetDateTime(i++);
TrainingTestRecord.TestResult = reader.GetString(i++);
TrainingTestRecord.TestPeople = reader.GetString(i++);
TrainingTestRecord.Remark = reader.GetString(i++);
return TrainingTestRecord;
} else {
return null;
}
}
}
public bool UpdateInsuranceFund(InsuranceFund InsuranceFund)
{
string sqlText = string.Format(@"update {0} set
InsuranceFundType=@InsuranceFundType,
[Number]=@Number,
PaymentAddress=@PaymentAddress,
PaymentStartDate=@PaymentStartDate,
PaymentEndDate=@PaymentEndDate,
BasicNum=@BasicNum
where InsuranceFundID=@InsuranceFundID", _InsuranceFundTableName);
var paras = new OleDbParameter[7];
paras[0] = new OleDbParameter("InsuranceFundType", InsuranceFund.InsuranceFundType);
paras[1] = new OleDbParameter("Number", InsuranceFund.Number);
paras[2] = new OleDbParameter("PaymentAddress", InsuranceFund.PaymentAddress);
paras[3] = new OleDbParameter("PaymentStartDate", InsuranceFund.PaymentStartDate);
paras[4] = new OleDbParameter("PaymentEndDate", InsuranceFund.PaymentEndDate);
paras[5] = new OleDbParameter("BasicNum", InsuranceFund.BasicNum);
paras[6] = new OleDbParameter("InsuranceFundID", InsuranceFund.InsuranceFundID);
var result = new OLESqlHelper().ExecuteNoneQuery(sqlText, paras);
return result;
}
public bool InsertInsuranceFund(InsuranceFund InsuranceFund)
{
string sqlText = string.Format(@"insert into {0}(
PeopleID,
InsuranceFundType,
[Number],
PaymentAddress,
PaymentStartDate,
PaymentEndDate,
BasicNum
)
values(
@PeopleID,
@InsuranceFundType,
@Number,
@PaymentAddress,
@PaymentStartDate,
@PaymentEndDate,
@BasicNum)", _InsuranceFundTableName);
var paras = new OleDbParameter[7];
paras[0] = new OleDbParameter("PeopleID", InsuranceFund.PeopleID);
paras[1] = new OleDbParameter("InsuranceFundType", InsuranceFund.InsuranceFundType);
paras[2] = new OleDbParameter("Number", InsuranceFund.Number);
paras[3] = new OleDbParameter("PaymentAddress", InsuranceFund.PaymentAddress);
paras[4] = new OleDbParameter("PaymentStartDate", InsuranceFund.PaymentStartDate);
paras[4].OleDbType = OleDbType.DBDate;
paras[5] = new OleDbParameter("PaymentEndDate", InsuranceFund.PaymentEndDate);
paras[5].OleDbType = OleDbType.DBDate;
paras[6] = new OleDbParameter("BasicNum", InsuranceFund.BasicNum);
var result = new OLESqlHelper().ExecuteNoneQuery(sqlText, paras);
return result;
}
public List<TitleWageChangeRecord> GetTitleWageChangeRecordList(int peopleID)
{
string sqlText = string.Format(@"select
TitleWageChangeRecordID,
ChangeDate,
ChangeType,
ContractNum,
BeforeChangeTitle,
BeforeChangeBasicWage,
BeforeChangeAllowance,
BeforeChangeYearAward,
AfterChangeTitle,
AfterChangeBasicWage,
AfterChangeAllowance,
AfterChangeYearAward
from {0}
where PeopleID=@PeopleID"
, _TitleWageChangeRecordTableName);
var paras = new OleDbParameter[1];
paras[0] = new OleDbParameter("PeopleID", peopleID);
var table = new OLESqlHelper().GetTable(sqlText, paras);
var TitleWageChangeRecordList = new List<TitleWageChangeRecord>();
foreach (DataRow row in table.Rows) {
var TitleWageChangeRecord = new TitleWageChangeRecord() {
TitleWageChangeRecordID = (int)row["TitleWageChangeRecordID"],
ChangeDate = (DateTime?)row["ChangeDate"],
ChangeType = (int)row["ChangeType"],
ContractNum = row["ContractNum"].ToString(),
BeforeChangeTitle = row["BeforeChangeTitle"].ToString(),
BeforeChangeBasicWage = (float)row["BeforeChangeBasicWage"],
BeforeChangeAllowance = (float)row["BeforeChangeAllowance"],
BeforeChangeYearAward = (float)row["BeforeChangeYearAward"],
AfterChangeTitle = row["AfterChangeTitle"].ToString(),
AfterChangeBasicWage = (float)row["AfterChangeBasicWage"],
AfterChangeAllowance = (float)row["AfterChangeAllowance"],
AfterChangeYearAward = (float)row["AfterChangeYearAward"]
};
TitleWageChangeRecordList.Add(TitleWageChangeRecord);
}
return TitleWageChangeRecordList;
}
public bool InsertTitleWageChangeRecord(TitleWageChangeRecord TitleWageChangeRecord)
{
string sqlText = string.Format(@"insert into {0}(
PeopleID,
ChangeDate,
ChangeType,
ContractNum,
BeforeChangeTitle,
BeforeChangeBasicWage,
BeforeChangeAllowance,
BeforeChangeYearAward,
AfterChangeTitle,
AfterChangeBasicWage,
AfterChangeAllowance,
AfterChangeYearAward
)
values(
@PeopleID,
@ChangeDate,
@ChangeType,
@ContractNum,
@BeforeChangeTitle,
@BeforeChangeBasicWage,
@BeforeChangeAllowance,
@BeforeChangeYearAward,
@AfterChangeTitle,
@AfterChangeBasicWage,
@AfterChangeAllowance,
@AfterChangeYearAward)", _TitleWageChangeRecordTableName);
var paras = new OleDbParameter[12];
paras[0] = new OleDbParameter("PeopleID", TitleWageChangeRecord.PeopleID);
paras[1] = new OleDbParameter("ChangeDate", TitleWageChangeRecord.ChangeDate);
paras[1].OleDbType = OleDbType.Date;
paras[2] = new OleDbParameter("ChangeType", TitleWageChangeRecord.ChangeType);
paras[3] = new OleDbParameter("ContractNum", TitleWageChangeRecord.ContractNum);
paras[4] = new OleDbParameter("BeforeChangeTitle", TitleWageChangeRecord.BeforeChangeTitle);
paras[5] = new OleDbParameter("BeforeChangeBasicWage", TitleWageChangeRecord.BeforeChangeBasicWage);
paras[6] = new OleDbParameter("BeforeChangeAllowance", TitleWageChangeRecord.BeforeChangeAllowance);
paras[7] = new OleDbParameter("BeforeChangeYearAward", TitleWageChangeRecord.BeforeChangeYearAward);
paras[8] = new OleDbParameter("AfterChangeTitle", TitleWageChangeRecord.AfterChangeTitle);
paras[9] = new OleDbParameter("AfterChangeBasicWage", TitleWageChangeRecord.AfterChangeBasicWage);
paras[10] = new OleDbParameter("AfterChangeAllowance", TitleWageChangeRecord.AfterChangeAllowance);
paras[11] = new OleDbParameter("AfterChangeYearAward", TitleWageChangeRecord.AfterChangeYearAward);
var result = new OLESqlHelper().ExecuteNoneQuery(sqlText, paras);
return result;
}
public bool InsertRewardRecord(RewardRecord rewardRecord)
{
string sqlText = string.Format(@"insert into {0}(
PeopleID,
RewardDate,
RewardContent
)
values(
@PeopleID,
@RewardDate,
@RewardContent
)", _RewardRecordTableName);
var paras = new OleDbParameter[3];
paras[0] = new OleDbParameter("PeopleID", rewardRecord.PeopleID);
paras[1] = new OleDbParameter("RewardDate", rewardRecord.RewardDate);
paras[1].OleDbType = OleDbType.DBDate;
paras[2] = new OleDbParameter("RewardContent", rewardRecord.RewardContent);
var result = new OLESqlHelper().ExecuteNoneQuery(sqlText, paras);
return result;
}
public bool UpdatePeople(PeopleBasicInfo people)
{
string sqlText = string.Format(@"update {0} set
WorkerNum=@WorkerNum,
PeopleName=@peopleName,
Gender=@gender,
DateOfBirth=@dateOfBirth,
Age=@Age,
IDCardNum=@IDCardNum,
IsMarried=@IsMarried,
PoliticalStatus=@PoliticalStatus,
Nation=@Nation,
HouseholdRegisterAddress=@HouseholdRegisterAddress,
HouseholdRegisterType=@HouseholdRegisterType,
HouseholdRegisterPostCode=@HouseholdRegisterPostCode,
CurrentAddress=@CurrentAddress,
PhoneNum=@PhoneNum,
CurrentAddressPostCode=@CurrentAddressPostCode,
EducationGrade=@EducationGrade,
GraduationSchool=@GraduationSchool,
Profession=@Profession,
EmergencyContactPerson=@EmergencyContactPerson,
EmergencyContactPersonPhone=@EmergencyContactPersonPhone,
EmergencyContactPersonRelation=@EmergencyContactPersonRelation
where PeopleID=@peopleID", _peopleTableName);
var paras = new OleDbParameter[22];
paras[0] = new OleDbParameter("WorkerNum", people.WorkerNum);
paras[1] = new OleDbParameter("peopleName", people.PeopleName);
paras[2] = new OleDbParameter("gender", people.Gender);
paras[3] = new OleDbParameter("dateOfBirth", people.DateOfBirth.Value);
paras[4] = new OleDbParameter("Age", people.Age);
paras[5] = new OleDbParameter("IDCardNum", people.IDCardNum);
paras[6] = new OleDbParameter("IsMarried", people.IsMarried);
paras[7] = new OleDbParameter("PoliticalStatus", people.PoliticalStatus);
paras[8] = new OleDbParameter("Nation", people.Nation);
paras[9] = new OleDbParameter("HouseholdRegisterAddress", people.HouseholdRegisterAddress);
paras[10] = new OleDbParameter("HouseholdRegisterType", people.HouseholdRegisterType);
paras[11] = new OleDbParameter("HouseholdRegisterPostCode", people.HouseholdRegisterPostCode);
paras[12] = new OleDbParameter("CurrentAddress", people.CurrentAddress);
paras[13] = new OleDbParameter("PhoneNum", people.PhoneNum);
paras[14] = new OleDbParameter("CurrentAddressPostCode", people.CurrentAddressPostCode);
paras[15] = new OleDbParameter("EducationGrade", people.EducationGrade);
paras[16] = new OleDbParameter("GraduationSchool", people.GraduationSchool);
paras[17] = new OleDbParameter("Profession", people.Profession);
paras[18] = new OleDbParameter("EmergencyContactPerson", people.EmergencyContactPerson);
paras[19] = new OleDbParameter("EmergencyContactPersonPhone", people.EmergencyContactPersonPhone);
paras[20] = new OleDbParameter("EmergencyContactPersonRelation", people.EmergencyContactPersonRelation);
paras[21] = new OleDbParameter("peopleID", people.PeopleID);
var result = new OLESqlHelper().ExecuteNoneQuery(sqlText, paras);
return result;
}
public bool UpdatePeopleWorkInfo(PeopleBasicInfo people)
{
string sqlText = string.Format(@"update {0} set
JoinCompanyDate=@JoinCompanyDate,
LengthOfService=@LengthOfService,
Department=@Department,
Title=@Title,
TitleState=@TitleState,
LeftCompanyDate=@LeftCompanyDate,
LeftCompanyReason=@LeftCompanyReason,
SpecialSkill=@SpecialSkill,
WageCardNumber=@WageCardNumber,
WageCardAddress=@WageCardAddress,
IsCallInArchives=@IsCallInArchives,
ArchivesNum=@ArchivesNum,
ArchiveCallInDate=@ArchiveCallInDate,
ArchiveCallOutDate=@ArchiveCallOutDate
where PeopleID=@PeopleID", _peopleTableName);
var paras = new OleDbParameter[15];
paras[0] = new OleDbParameter("JoinCompanyDate", people.JoinCompanyDate);
paras[0].OleDbType = OleDbType.DBDate;
paras[1] = new OleDbParameter("LengthOfService", people.LengthOfService);
paras[1].OleDbType = OleDbType.Single;
paras[2] = new OleDbParameter("Department", people.Department);
paras[3] = new OleDbParameter("Title", people.Title);
paras[4] = new OleDbParameter("TitleState", people.TitleState);
paras[5] = new OleDbParameter("LeftCompanyDate", people.LeftCompanyDate);
paras[5].OleDbType = OleDbType.DBDate;
paras[6] = new OleDbParameter("LeftCompanyReason", people.LeftCompanyReason);
paras[7] = new OleDbParameter("SpecialSkill", people.SpecialSkill);
paras[8] = new OleDbParameter("WageCardNumber", people.WageCardNumber);
paras[9] = new OleDbParameter("WageCardAddress", people.WageCardAddress);
paras[10] = new OleDbParameter("IsCallInArchives", people.IsCallInArchives);
paras[11] = new OleDbParameter("ArchivesNum", people.ArchivesNum);
paras[12] = new OleDbParameter("ArchiveCallInDate", people.ArchiveCallInDate);
paras[12].OleDbType = OleDbType.DBDate;
paras[13] = new OleDbParameter("ArchiveCallOutDate", people.ArchiveCallOutDate);
paras[13].OleDbType = OleDbType.DBDate;
paras[14] = new OleDbParameter("PeopleID", people.PeopleID);
// string sqlText = string.Format(@"update {0} set
// LengthOfService=@LengthOfService
// where PeopleID=@PeopleID", _ContractTableName);
// var paras = new OleDbParameter[2];
// paras[0] = new OleDbParameter("LengthOfService", contract.LengthOfService);
// //paras[5] = new OleDbParameter("LeftCompanyDate", contract.LeftCompanyDate.Value);
// //paras[6] = new OleDbParameter("LeftCompanyReason", contract.LeftCompanyReason);
// paras[1] = new OleDbParameter("PeopleID", contract.PeopleID);
var result = new OLESqlHelper().ExecuteNoneQuery(sqlText, paras);
return result;
}
public bool UpdateRewardRecord(RewardRecord RewardRecord)
{
string sqlText = string.Format(@"update {0} set
RewardDate=@RewardDate,
RewardContent=@RewardContent
where RecordID=@RecordID", _RewardRecordTableName);
var paras = new OleDbParameter[3];
paras[0] = new OleDbParameter("RewardDate", RewardRecord.RewardDate);
paras[0].OleDbType = OleDbType.DBDate;
paras[1] = new OleDbParameter("RewardContent", RewardRecord.RewardContent);
paras[2] = new OleDbParameter("RecordID", RewardRecord.RecordID);
var result = new OLESqlHelper().ExecuteNoneQuery(sqlText, paras);
return result;
}
public bool UpdateTitleWageChangeRecord(TitleWageChangeRecord TitleWageChangeRecord)
{
string sqlText = string.Format(@"update {0} set
ChangeDate=@ChangeDate,
ChangeType=@ChangeType,
ContractNum=@ContractNum,
BeforeChangeTitle=@BeforeChangeTitle,
BeforeChangeBasicWage=@BeforeChangeBasicWage,
BeforeChangeallowance=@BeforeChangeAllowance,
BeforeChangeYearAward=@BeforeChangeYearAward,
AfterChangeTitle=@AfterChangeTitle,
AfterChangeBasicWage=@AfterChangeBasicWage,
AfterChangeAllowance=@AfterChangeAllowance,
AfterChangeYearAward=@AfterChangeYearAward
where TitleWageChangeRecordID=@TitleWageChangeRecordID", _TitleWageChangeRecordTableName);
var paras = new OleDbParameter[12];
paras[0] = new OleDbParameter("ChangeDate", TitleWageChangeRecord.ChangeDate);
paras[0].OleDbType = OleDbType.DBDate;
paras[1] = new OleDbParameter("ChangeType", TitleWageChangeRecord.ChangeType);
paras[2] = new OleDbParameter("ContractNum", TitleWageChangeRecord.ContractNum);
paras[3] = new OleDbParameter("BeforeChangeTitle", TitleWageChangeRecord.BeforeChangeTitle);
paras[4] = new OleDbParameter("BeforeChangeBasicWage", TitleWageChangeRecord.BeforeChangeBasicWage);
paras[5] = new OleDbParameter("BeforeChangeAllowance", TitleWageChangeRecord.BeforeChangeAllowance);
paras[6] = new OleDbParameter("BeforeChangeYearAward", TitleWageChangeRecord.BeforeChangeYearAward);
paras[7] = new OleDbParameter("AfterChangeTitle", TitleWageChangeRecord.AfterChangeTitle);
paras[8] = new OleDbParameter("AfterChangeBasicWage", TitleWageChangeRecord.AfterChangeBasicWage);
paras[9] = new OleDbParameter("AfterChangeAllowance", TitleWageChangeRecord.AfterChangeAllowance);
paras[10] = new OleDbParameter("AfterChangeYearAward", TitleWageChangeRecord.AfterChangeYearAward);
paras[11] = new OleDbParameter("TitleWageChangeRecordID", TitleWageChangeRecord.TitleWageChangeRecordID);
var result = new OLESqlHelper().ExecuteNoneQuery(sqlText, paras);
return result;
}