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 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 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 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 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 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 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 PeopleBasicInfo GetPeopleWorkInfoByID(int peopleID)
{
using (var connection = new OleDbConnection(ConfigManagement.AccessConStr)) {
string sqlText = string.Format(@"select PeopleID,JoinCompanyDate,LengthOfService,Department,Title,TitleState,
LeftCompanyDate,LeftCompanyReason,SpecialSkill,WageCardNumber,WageCardAddress,
IsCallInArchives,ArchivesNum,ArchiveCallInDate,ArchiveCallOutDate
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++);
if (!reader.IsDBNull(i)) {
people.JoinCompanyDate = reader.GetDateTime(i++);
} else {
i++;
}
people.LengthOfService = reader.GetFloat(i++);
people.Department = reader.IsDBNull(i) ? "" : reader.GetString(i++);
people.Title = reader.IsDBNull(i) ? "" : reader.GetString(i++);
people.TitleState = reader.IsDBNull(i) ? "" : reader.GetString(i++);
people.LeftCompanyDate = reader.IsDBNull(i) ? new Nullable<DateTime>() : reader.GetDateTime(i++);
people.LeftCompanyReason = reader.IsDBNull(i) ? "" : reader.GetString(i++);
people.SpecialSkill = reader.IsDBNull(i) ? "" : reader.GetString(i++);
people.WageCardNumber = reader.IsDBNull(i) ? "" : reader.GetString(i++);
people.WageCardAddress = reader.IsDBNull(i) ? "" : reader.GetString(i++);
people.IsCallInArchives = reader.IsDBNull(i) ? false : reader.GetBoolean(i++);
people.ArchivesNum = reader.IsDBNull(i) ? "" : reader.GetString(i++);
people.ArchiveCallInDate = reader.IsDBNull(i) ? new Nullable<DateTime>() : reader.GetDateTime(i++);
people.ArchiveCallOutDate = reader.IsDBNull(i) ? new Nullable<DateTime>() : reader.GetDateTime(i++);
return people;
} else {
return null;
}
}
}