public List <StudentModel> SearchStudent(string fullname, int schoolId, string identityNumber, int graduatingYear)
{
List <StudentModel> studentModels = new List <StudentModel>();
using (conn = JBCertConnection.Instance)
{
string queryString = @"SELECT a.*, b.Tentruong, c.Hinhthucdaotao, d.TenDantoc, e.name as 'Tenloai', f.Tenxeploai
FROM [jb_cert].[dbo].[tblHocsinh] as a Left join [jb_cert].[dbo].[tblTruong] as b
On a.[TruongId] = b.[Id]
Left join [jb_cert].[dbo].[tblHinhthucdaotao] as c
on a.HinhthucdaotaoId = c.Id
left join [jb_cert].[dbo].[tblDantoc] as d
on a.DantocId = d.Id
left join [jb_cert].[dbo].[tblLoai] as e
on a.LoaiId = e.Id
left join [jb_cert].[dbo].[tblXeploai] as f
on a.XeploaiId = f.Id
Where a.[IsDeleted] = 0
and a.[Ten] like @StudentName
and (@SchoolId = -1 or a.[TruongId] = @SchoolId)
and a.[CMT] like @CMT
and (@GraduatingYear = -1 or a.[Namtotnghiep] = @GraduatingYear)";
conn.Open();
SqlCommand sqlCommand = new SqlCommand(queryString, conn);
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.AddWithValue("@StudentName", "%" + fullname + "%");
sqlCommand.Parameters.AddWithValue("@SchoolId", schoolId);
sqlCommand.Parameters.AddWithValue("@CMT", "%" + identityNumber + "%");
sqlCommand.Parameters.AddWithValue("@GraduatingYear", graduatingYear);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
try
{
while (sqlDataReader.Read())
{
StudentModel studentModel = new StudentModel();
studentModel.Id = int.Parse(sqlDataReader["Id"].ToString());
studentModel.SchoolId = int.Parse(sqlDataReader["TruongId"].ToString());
studentModel.SchoolName = sqlDataReader["Tentruong"].ToString();
studentModel.EthnicId = int.Parse(sqlDataReader["DantocId"].ToString());
studentModel.EthnicName = sqlDataReader["Tendantoc"].ToString();
studentModel.FullName = sqlDataReader["Ten"].ToString();
studentModel.Address = sqlDataReader["Diachi"].ToString();
studentModel.Dob = DateTime.Parse(sqlDataReader["Ngaysinh"].ToString());
studentModel.HouseHold = sqlDataReader["Hokhau"].ToString();
studentModel.IdentityNumber = sqlDataReader["CMT"].ToString();
studentModel.Gender = sqlDataReader["Gioitinh"].ToString();
studentModel.Note = sqlDataReader["Ghichu"].ToString();
studentModel.Image = sqlDataReader["Anh"].ToString();
studentModel.IsDeleted = false;
studentModel.BlankCertTypeId = int.Parse(sqlDataReader["LoaiId"].ToString());
studentModel.BlankCertTypeName = sqlDataReader["Tenloai"].ToString();
studentModel.BornedAddress = sqlDataReader["Noisinh"].ToString();
studentModel.LearningModeId = int.Parse(sqlDataReader["HinhthucdaotaoId"].ToString());
studentModel.LearningModeName = sqlDataReader["Hinhthucdaotao"].ToString();
studentModel.Score = float.Parse(sqlDataReader["Score"].ToString());
studentModel.MajorId = int.Parse(sqlDataReader["NganhdaotaoId"].ToString());
studentModel.GraduatingYear = int.Parse(sqlDataReader["Namtotnghiep"].ToString());
studentModel.RankingId = int.Parse(sqlDataReader["XeploaiId"].ToString());
studentModel.RankingName = sqlDataReader["Tenxeploai"].ToString();
studentModels.Add(studentModel);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlDataReader.Close();
conn.Close();
}
}
return(studentModels);
}
public List <StudentModel> SearchStudentForAddingCert(string studentName, int schoolId)
{
List <StudentModel> studentModels = new List <StudentModel>();
using (conn = JBCertConnection.Instance)
{
string queryString = @"SELECT *, b.Tenxeploai FROM [dbo].[tblHocsinh] as a
left join [dbo].[tblXeploai] as b
on a.XeploaiId = b.Id
where a.[IsDeleted] = 0 and a.[Ten] like @StudentName
and a.Id not in (select HocsinhId from [dbo].[tblBang] where LoaiId = a.LoaiId and IsDeleted = 0 )" ;
string queryString1 = @"SELECT *, b.Tenxeploai FROM [dbo].[tblHocsinh] as a
left join [dbo].[tblXeploai] as b
on a.XeploaiId = b.Id
where a.[IsDeleted] = 0 and a.[Ten] like @StudentName and a.[TruongId] = @SchoolId
and a.Id not in (select HocsinhId from [dbo].[tblBang] where LoaiId = a.LoaiId and IsDeleted = 0 )" ;
conn.Open();
SqlCommand sqlCommand;
if (schoolId == -1)
{
sqlCommand = new SqlCommand(queryString, conn);
sqlCommand.Parameters.AddWithValue("@StudentName", "%" + studentName + "%");
}
else
{
sqlCommand = new SqlCommand(queryString1, conn);
sqlCommand.Parameters.AddWithValue("@StudentName", "%" + studentName + "%");
sqlCommand.Parameters.AddWithValue("@SchoolId", schoolId);
}
sqlCommand.CommandType = CommandType.Text;
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
try
{
while (sqlDataReader.Read())
{
StudentModel studentModel = new StudentModel();
studentModel.Id = int.Parse(sqlDataReader["Id"].ToString());
studentModel.SchoolId = int.Parse(sqlDataReader["TruongId"].ToString());
studentModel.EthnicId = int.Parse(sqlDataReader["DantocId"].ToString());
studentModel.FullName = sqlDataReader["Ten"].ToString();
studentModel.IdentityNumber = sqlDataReader["CMT"].ToString();
studentModel.Gender = sqlDataReader["Gioitinh"].ToString();
studentModel.Image = sqlDataReader["Anh"].ToString();
studentModel.IsDeleted = false;
studentModel.RankingId = int.Parse(sqlDataReader["XeploaiId"].ToString());
studentModel.RankingName = sqlDataReader["Tenxeploai"].ToString();
studentModels.Add(studentModel);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlDataReader.Close();
conn.Close();
}
}
return(studentModels);
}