public CResult<bool> AddPeople(WebPeopleBasicInfo webPeople)
 {
     var people = new PeopleBasicInfo() {
         WorkerNum = webPeople.WorkerNum,
         PeopleName = webPeople.PeopleName,
         DateOfBirth = webPeople.DateOfBirth,
         Gender = webPeople.Gender,
         Age = webPeople.Age,
         IDCardNum = webPeople.IDCardNum,
         IsMarried = webPeople.IsMarried,
         PoliticalStatus = webPeople.PoliticalStatus,
         Nation = webPeople.Nation,
         HouseholdRegisterAddress = webPeople.HouseholdRegisterAddress,
         HouseholdRegisterType = webPeople.HouseholdRegisterType,
         HouseholdRegisterPostCode = webPeople.HouseholdRegisterPostCode,
         CurrentAddress = webPeople.CurrentAddress,
         PhoneNum = webPeople.PhoneNum,
         CurrentAddressPostCode = webPeople.CurrentAddressPostCode,
         EducationGrade = webPeople.EducationGrade,
         GraduationSchool = webPeople.GraduationSchool,
         Profession = webPeople.Profession,
         EmergencyContactPerson = webPeople.EmergencyContactPerson,
         EmergencyContactPersonPhone = webPeople.EmergencyContactPersonPhone,
         EmergencyContactPersonRelation = webPeople.EmergencyContactPersonRelation
     };
     var result = new PeopleDAL().InsertPeople(people);
     if (result) {
         return new CResult<bool>(true);
     } else {
         return new CResult<bool>(false, ErrorCode.SaveDataFailed);
     }
 }
        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 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 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 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 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;
                }
            }
        }
 public CResult<bool> UpdatePeopleWorkInfo(WebPeopleWorkInfo webPeopleWorkInfo)
 {
     var people = new PeopleBasicInfo() {
         PeopleID = webPeopleWorkInfo.PeopleID,
         JoinCompanyDate = webPeopleWorkInfo.JoinCompanyDate,
         LengthOfService = webPeopleWorkInfo.LengthOfService,
         Department = webPeopleWorkInfo.Department,
         Title = webPeopleWorkInfo.Title,
         TitleState = webPeopleWorkInfo.TitleState,
         LeftCompanyDate = webPeopleWorkInfo.LeftCompanyDate,
         LeftCompanyReason = webPeopleWorkInfo.LeftCompanyReason,
         SpecialSkill = webPeopleWorkInfo.SpecialSkill,
         WageCardNumber = webPeopleWorkInfo.WageCardNumber,
         WageCardAddress = webPeopleWorkInfo.WageCardAddress,
         IsCallInArchives = webPeopleWorkInfo.IsCallInArchives,
         ArchivesNum = webPeopleWorkInfo.ArchivesNum,
         ArchiveCallInDate = webPeopleWorkInfo.ArchiveCallInDate,
         ArchiveCallOutDate = webPeopleWorkInfo.ArchiveCallOutDate,
     };
     var result = new PeopleDAL().UpdatePeopleWorkInfo(people);
     if (result) {
         return new CResult<bool>(true);
     } else {
         return new CResult<bool>(false, ErrorCode.SaveDataFailed);
     }
 }