public IList<PracticeRcd> GetByStdCode(string stdcode)
{
IList<Core.Business.PracticeRcd> practiceRcdlist = new List<Core.Business.PracticeRcd>();
SqlServerUtility sql = new SqlServerUtility();
sql.AddParameter("@StdCode", SqlDbType.NVarChar, stdcode);
SqlDataReader reader = sql.ExecuteSqlReader(SqlGetByStdCode);
if (reader != null)
{
while (reader.Read())
{
Core.Business.PracticeRcd practiceRcd = new Core.Business.PracticeRcd();
if (!reader.IsDBNull(0)) practiceRcd.Id = reader.GetInt32(0);
if (!reader.IsDBNull(1)) practiceRcd.StdCode = reader.GetString(1);
if (!reader.IsDBNull(2)) practiceRcd.TeacherCode = reader.GetString(2);
if (!reader.IsDBNull(3)) practiceRcd.StartDate = reader.GetDateTime(3);
if (!reader.IsDBNull(4)) practiceRcd.EndDate = reader.GetDateTime(4);
if (!reader.IsDBNull(5)) practiceRcd.EntCode = reader.GetString(5);
if (!reader.IsDBNull(6)) practiceRcd.ReturnReson = reader.GetString(6);
if (!reader.IsDBNull(7)) practiceRcd.TeamCate = reader.GetString(7);
if (!reader.IsDBNull(8)) practiceRcd.TeamPhone = reader.GetString(8);
if (!reader.IsDBNull(9)) practiceRcd.PratiseState = reader.GetString(9);
practiceRcd.MarkOld();
practiceRcdlist.Add(practiceRcd);
}
reader.Close();
}
return practiceRcdlist;
}
public IList<Core.Business.PracticeRcd> Search(string code, string team)
{
IList<Core.Business.PracticeRcd> practiceRcdlist = new List<Core.Business.PracticeRcd>();
SqlServerUtility sql = new SqlServerUtility();
string sqlstr = SqlGetAllPracticeRcd;
if (code == string.Empty || code == "" || (String.IsNullOrEmpty(code)))
{
sql.AddParameter("@EntCode", SqlDbType.NVarChar, team);
sqlstr = SqlSearchByEnt;
}
else if (team == string.Empty || team == "" || (String.IsNullOrEmpty(team)))
{
sql.AddParameter("@StdCode", SqlDbType.NVarChar, code);
sqlstr = SqlSearchByStd;
}
else
{
sql.AddParameter("@EntCode", SqlDbType.NVarChar, team);
sql.AddParameter("@StdCode", SqlDbType.NVarChar, code);
sqlstr = SqlSearchByStdAndEnt;
}
SqlDataReader reader = sql.ExecuteSqlReader(sqlstr);
if (reader != null && !reader.IsClosed)
{
while (reader.Read())
{
Core.Business.PracticeRcd practiceRcd = new Core.Business.PracticeRcd();
if (!reader.IsDBNull(0)) practiceRcd.Id = reader.GetInt32(0);
if (!reader.IsDBNull(1)) practiceRcd.StdCode = reader.GetString(1);
if (!reader.IsDBNull(2)) practiceRcd.TeacherCode = reader.GetString(2);
if (!reader.IsDBNull(3)) practiceRcd.StartDate = reader.GetDateTime(3);
if (!reader.IsDBNull(4)) practiceRcd.EndDate = reader.GetDateTime(4);
if (!reader.IsDBNull(5)) practiceRcd.EntCode = reader.GetString(5);
if (!reader.IsDBNull(6)) practiceRcd.ReturnReson = reader.GetString(6);
if (!reader.IsDBNull(7)) practiceRcd.TeamCate = reader.GetString(7);
if (!reader.IsDBNull(8)) practiceRcd.TeamPhone = reader.GetString(8);
if (!reader.IsDBNull(9)) practiceRcd.PratiseState = reader.GetString(9);
practiceRcd.MarkOld();
practiceRcdlist.Add(practiceRcd);
}
reader.Close();
}
return practiceRcdlist;
}
public Core.Business.PracticeRcd Select(int id)
{
SqlServerUtility sql = new SqlServerUtility();
sql.AddParameter("@Id", SqlDbType.Int, id);
SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectPracticeRcd);
if (reader != null && !reader.IsClosed && reader.Read())
{
Core.Business.PracticeRcd practiceRcd = new Core.Business.PracticeRcd();
if (!reader.IsDBNull(0)) practiceRcd.Id = reader.GetInt32(0);
if (!reader.IsDBNull(1)) practiceRcd.StdCode = reader.GetString(1);
if (!reader.IsDBNull(2)) practiceRcd.TeacherCode = reader.GetString(2);
if (!reader.IsDBNull(3)) practiceRcd.StartDate = reader.GetDateTime(3);
if (!reader.IsDBNull(4)) practiceRcd.EndDate = reader.GetDateTime(4);
if (!reader.IsDBNull(5)) practiceRcd.EntCode = reader.GetString(5);
if (!reader.IsDBNull(6)) practiceRcd.ReturnReson = reader.GetString(6);
if (!reader.IsDBNull(7)) practiceRcd.TeamCate = reader.GetString(7);
if (!reader.IsDBNull(8)) practiceRcd.TeamPhone = reader.GetString(8);
if (!reader.IsDBNull(9)) practiceRcd.PratiseState = reader.GetString(9);
reader.Close();
return practiceRcd;
}
else
{
if (reader != null && !reader.IsClosed)
reader.Close();
return null;
}
}
public IList<PracticeRcd> GetStatisticsByTime(string StartTime, string EndTime)
{
string sqlGetStatistics = " SELECT count([PracticeRcd].[StdCode]),[TeacherCode],[StartDate],[EndDate],[EntCode],"
+ "[ReturnReson],[TeamCate],[TeamPhone],[PratiseState] ,[ClassInfo].[ClassName]"
+ " FROM [PracticeRcd],[Student],[StdClass],[ClassInfo] "
+ " where [PracticeRcd].[StdCode]=[Student].[Code] AND [Student].[Code]=[StdClass].[StdCode] AND "
+ " [PracticeRcd].[StdCode]=[StdClass].[StdCode] AND [ClassInfo].[ClassCode]=[StdClass].[ClassCode] "
+ " AND [PracticeRcd].[StartDate]>'" + DateTime.Parse(StartTime).ToString() + "' AND [PracticeRcd].[StartDate]<'" + DateTime.Parse(EndTime).ToString() + "' "
+ " group by [TeacherCode],[StartDate],[EndDate],[EntCode], "
+ " [ReturnReson],[TeamCate],[TeamPhone],[PratiseState] ,[ClassInfo].[ClassName]"
+ " order by [StartDate] DESC,[EndDate] DESC ";
IList<Core.Business.PracticeRcd> practiceRcdlist = new List<Core.Business.PracticeRcd>();
SqlServerUtility sql = new SqlServerUtility();
SqlDataReader reader = sql.ExecuteSqlReader(sqlGetStatistics);
if (reader != null)
{
while (reader.Read())
{
Core.Business.PracticeRcd practiceRcd = new Core.Business.PracticeRcd();
if (!reader.IsDBNull(0)) practiceRcd.StdCount = reader.GetInt32(0);
if (!reader.IsDBNull(1)) practiceRcd.TeacherCode = reader.GetString(1);
if (!reader.IsDBNull(2)) practiceRcd.StartDate = reader.GetDateTime(2);
if (!reader.IsDBNull(3)) practiceRcd.EndDate = reader.GetDateTime(3);
if (!reader.IsDBNull(4)) practiceRcd.EntCode = reader.GetString(4);
if (!reader.IsDBNull(5)) practiceRcd.ReturnReson = reader.GetString(5);
if (!reader.IsDBNull(6)) practiceRcd.TeamCate = reader.GetString(6);
if (!reader.IsDBNull(7)) practiceRcd.TeamPhone = reader.GetString(7);
if (!reader.IsDBNull(8)) practiceRcd.PratiseState = reader.GetString(8);
if (!reader.IsDBNull(9)) practiceRcd.StatisticsClass = reader.GetString(9);
practiceRcd.MarkOld();
practiceRcdlist.Add(practiceRcd);
}
reader.Close();
}
return practiceRcdlist;
}
