public List <Score> GetList()
{
string sql = "SELECT s.ID,s.TypeID,s.Number,s.Name,s.Sex,s.Birth,s.Grade,s.Unit,s.Job,s.Phone,s.ValidTime,s.CreatTime,s.Creator,t.TypeName FROM score s inner join scoretype t on t.ID=s.TypeID where t.isdele=0 order by s.ID desc";
List <Score> list = DBHelp.GetSelect <Score>(sql);
return(list);
}
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/// <summary>
/// 考试类型
/// </summary>
/// <returns></returns>
public List <ScoreType> GetTypeList()
{
string sql = "SELECT * FROM scoretype where Isdele=0 order by ID desc";
List <ScoreType> list = DBHelp.GetSelect <ScoreType>(sql);
return(list);
}
/// <summary>
/// 用户
/// </summary>
/// <returns></returns>
public List <Users> GetUsers(string Name)
{
string sql = "SELECT * FROM Users where Isdele=0 and Name='" + Name + "' order by ID desc";
List <Users> list = DBHelp.GetSelect <Users>(sql);
return(list);
}
/// <summary>
/// 用户
/// </summary>
/// <returns></returns>
public List <Users> GetUsersList()
{
string sql = "SELECT * FROM Users where Isdele=0 order by ID desc";
List <Users> list = DBHelp.GetSelect <Users>(sql);
return(list);
}
public List <Score> GetListPageWhere(int PageSize, int PageCurr, string TypeName, string Name, string Number, string ValidTimeStra, string ValidTimeEnd, ref int Count, ref int Pages)
{
try
{
string sqlwhere = "";
if (!string.IsNullOrEmpty(TypeName))
{
sqlwhere += " and s.TypeID='" + TypeName + "'";
}
if (!string.IsNullOrEmpty(Name))
{
sqlwhere += " and s.Name like '%" + Name + "%'";
}
if (!string.IsNullOrEmpty(Number))
{
sqlwhere += " and s.Number like '%" + Number + "%'";
}
if (!string.IsNullOrEmpty(ValidTimeStra))
{
sqlwhere += " and s.ValidTime >= '" + ValidTimeStra + "'";
}
if (!string.IsNullOrEmpty(ValidTimeEnd))
{
sqlwhere += " and s.ValidTime <= '" + ValidTimeEnd + "'";
}
string whe = string.Format(@"SELECT count(0) FROM score s inner join scoretype t on t.ID=s.TypeID where t.isdele=0 {0} order by s.ID desc", sqlwhere);
string CountRows = DBHelp.GetSelectStr(whe);
Count = Int32.Parse(CountRows);
Pages = Int32.Parse(CountRows) % PageSize == 0 ? Int32.Parse(CountRows) % PageSize : Int32.Parse(CountRows) / PageSize + 1;
string sql = string.Format(@"SELECT s.ID,s.TypeID,s.Number,s.Name,s.Sex,s.Birth,s.Grade,s.Unit,s.Job,s.Phone,s.ValidTime,s.CreatTime,s.Creator,t.TypeName FROM score s inner join scoretype t on t.ID=s.TypeID where t.isdele=0 {0} order by s.ID desc limit {1}, {2}", sqlwhere, (PageSize * (PageCurr - 1)), PageSize);
List <Score> list = DBHelp.GetSelect <Score>(sql);
return(list);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 前台查询考试结果
/// </summary>
/// <param name="TypeName"></param>
/// <param name="Name"></param>
/// <param name="Number"></param>
/// <returns></returns>
public List <Score> GetListWhere(string TypeName, string Name, string Number)
{
string sqlwhere = "";
if (!string.IsNullOrEmpty(TypeName))
{
sqlwhere += " and s.TypeID='" + TypeName + "'";
}
if (!string.IsNullOrEmpty(Name))
{
sqlwhere += " and s.Name='" + Name + "'";
}
if (!string.IsNullOrEmpty(Number))
{
sqlwhere += " and s.Number='" + Number + "'";
}
string VTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); //有效日期
string sql = string.Format(@"SELECT s.ID,s.TypeID,s.Number,s.Name,s.Sex,s.Birth,s.Grade,s.Unit,s.Job,s.Phone,s.ValidTime,s.CreatTime,s.Creator,t.TypeName FROM score s inner join scoretype t on t.ID=s.TypeID where t.isdele=0 and s.ValidTime>='{1}' {0} order by s.ID desc", sqlwhere, VTime);
List <Score> list = DBHelp.GetSelect <Score>(sql);
return(list);
}