public List<WorkRecordManagePeople> SearchStaffDataBaseWork(int indexpage, SearchStaff SearchStaffConditionData) { List<WorkRecordManagePeople> returnValue = new List<WorkRecordManagePeople>(); DataBase Base = new DataBase(); string SearchStaffCondition = "and (ResignationDate='1900-01-01' or ResignationDate>@SearchDate ) and AppointmentDate<@SearchDate"; using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY unit,StaffID asc) " + "AS RowNum, StaffDatabase.* " + "FROM StaffDatabase WHERE isDeleted=0 " + SearchStaffCondition + " ) " + "AS NewTable " + "WHERE RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage) "; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage; cmd.Parameters.Add("@SearchDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStaffConditionData.txtstaffBirthdayStart); cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStaffConditionData.txtstaffID); cmd.Parameters.Add("@StaffName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStaffConditionData.txtstaffName) + "%"; cmd.Parameters.Add("@sex", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStaffConditionData.txtstaffSex); cmd.Parameters.Add("@sBirthdayStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStaffConditionData.txtstaffBirthdayStart); cmd.Parameters.Add("@sBirthdayEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStaffConditionData.txtstaffBirthdayEnd); cmd.Parameters.Add("@WorkItem", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStaffConditionData.txtstaffJob); cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStaffConditionData.txtstaffUnit); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { WorkRecordManagePeople addValue = new WorkRecordManagePeople(); addValue.StaffID = dr["StaffID"].ToString(); addValue.StaffName = dr["StaffName"].ToString(); addValue.WorkRecord = SearchStaffWorkRecord(dr["StaffID"].ToString(), SearchStaffConditionData.txtstaffBirthdayStart); WorkRecordManage thisValue = new WorkRecordManage();//新增搜尋方法 thisValue.Date = SearchStaffConditionData.txtstaffBirthdayStart; thisValue.StaffID = dr["StaffID"].ToString(); addValue.WorkRecordManage = GetWorkRecordManage(thisValue); returnValue.Add(addValue); } dr.Close(); Sqlconn.Close(); } catch (Exception e) { string Ex = e.Message.ToString(); //StaffDataList addValue = new StaffDataList(); //addValue.checkNo = "-1"; //addValue.errorMsg = e.Message.ToString(); //returnValue.Add(addValue); } } return returnValue; }
public List<WorkRecordManage> GetWorkRecordManage(WorkRecordManage SearchStaffCondition) { StaffDataBase sDB = new StaffDataBase(); return sDB.GetWorkRecordManage(SearchStaffCondition); }
public List<WorkRecordManage> GetWorkRecordManage(WorkRecordManage SearchStaffCondition) { List<WorkRecordManage> returnValue = new List<WorkRecordManage>(); DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = " select * from WorkRecordManage where date = @Date and StaffID = @StaffID "; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(SearchStaffCondition.StaffID); cmd.Parameters.Add("@Date", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStaffCondition.Date); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { WorkRecordManage addValue = new WorkRecordManage(); addValue.Date = DateTime.Parse(dr["Date"].ToString()).ToString("yyyy-MM-dd"); ; addValue.StartTime = dr["StartHour"].ToString(); addValue.StartMin = dr["StartMin"].ToString(); addValue.EndTime = dr["EndHour"].ToString(); addValue.EndMin = dr["EndMin"].ToString(); addValue.VacationType = dr["VacationType"].ToString(); addValue.VacationMark = dr["VacationMark"].ToString(); addValue.RealStart = dr["starttime"].ToString(); addValue.RealEnd = dr["endtime"].ToString(); returnValue.Add(addValue); } // returnValue[0] = cmd.ExecuteNonQuery().ToString(); Sqlconn.Close(); } catch (Exception e) { //returnValue[0] = "-1"; string ex = e.Message.ToString(); // returnValue[1] = e.Message.ToString(); } } return returnValue; }