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 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 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;
                }
            }
        }
Beispiel #10
0
        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;
                }
            }
        }