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;
}
