public Students GetAllStudents(Student.Includes includes, int?from, int?amount)
{
IList <DbParameter> parameters = new List <DbParameter>()
{
new MySqlParameter("@fromRow", from),
new MySqlParameter("@amount", amount)
};
string limitStatement = string.Empty;
if (from != null && amount != null)
{
limitStatement += $" Limit @fromRow, @amount ";
}
string sql = "CREATE TEMPORARY TABLE tmp_students " + GetQueryForMultipleTables(includes) + ";" +
$" CREATE TEMPORARY TABLE tmp_students_ids select distinct {Tables.Students.Id.FullName} from tmp_students " + limitStatement + ";" +
$" SELECT * from tmp_students where {Tables.Students.Id.FullName} in(SELECT * from tmp_students_ids);" +
$" DROP TEMPORARY TABLE tmp_students, tmp_students_ids; ";
DataTable table = _dbContext.GetDataTable(sql, parameters);
if (table != null)
{
return(StudentConverter.TableToStudent(table, includes));
}
return(new Students());
}
public async Task InsertStudent(ApiModel.Student student, CancellationToken cancellationToken)
{
var dataStudent = StudentConverter.ApiToEntityModel(student);
await _dbContext.InsertNewStudent(dataStudent, cancellationToken);
return;
}
public StudentBusiness(IStudentRepository studentRepository, ISendEmailService sendEmailService, GenerateTokenService tokenService, IDisciplineBusiness disciplineBusiness)
{
_studentRepository = studentRepository;
_emailSender = sendEmailService;
_tokenService = tokenService;
_disciplineBusiness = disciplineBusiness;
_studentConverter = new StudentConverter();
_studentDisciplineConverter = new StudentDisciplineConverter();
_disciplineConverter = new DisciplineConverter();
_authStudentConverter = new AuthStudentConverter();
}
public async Task <List <ApiModel.Student> > GetAllStudents(CancellationToken token)
{
var students = new List <ApiModel.Student>();
var dataStudents = await _dbContext.GetStudents(token);
foreach (var student in dataStudents)
{
var apiStudent = StudentConverter.EntityToApiModel(student);
students.Add(apiStudent);
}
return(students);
}
public Student GetPendingStudent(int studentId)
{
IList <DbParameter> parameters = new List <DbParameter>()
{
new MySqlParameter("@id", studentId),
new MySqlParameter("@status", "pending"),
new MySqlParameter("@is_active", false)
};
string sql = $"SELECT * FROM {Tables.Students.TableName} JOIN {Tables.BranchStudents.TableName} ON " +
$" {Tables.Students.TableName}.{Tables.Students.Id.Name} = {Tables.BranchStudents.TableName}.{Tables.BranchStudents.Id.Name}" +
$" WHERE {Tables.BranchStudents.Status.Name}=@status AND {Tables.BranchStudents.IsActive.Name}=@is_active AND {Tables.Students.Id.Name}=@number";
DataTable dataTable = _dbContext.GetDataTable(sql, parameters);
return(StudentConverter.RowToStudent(dataTable.AsEnumerable(), Student.Includes.None));
}
public Student GetStudentByIdentityNumber(int studentIdentityNumber, Student.Includes includes)
{
IList <DbParameter> parameters = new List <DbParameter>()
{
new MySqlParameter("@identity_number", studentIdentityNumber)
};
string sql = GetQueryForMultipleTables(includes) + $" WHERE {Tables.Students.TableName}.{Tables.Students.IdentityNumber.Name}=@identity_number";
DataTable table = _dbContext.GetDataTable(sql, parameters);
if (table != null)
{
return(StudentConverter.RowToStudent(table.AsEnumerable(), includes));
}
return(null);
}
public Students GetStudentsOfBranch(int branchId, Student.Includes includes, int?from, int?amount)
{
IList <DbParameter> parameters = new List <DbParameter>()
{
new MySqlParameter("@id", branchId),
new MySqlParameter("@fromRow", from),
new MySqlParameter("@amount", amount)
};
string sql = GetQueryForMultipleTables(includes) + $" WHERE {Tables.Branches.TableName}.{Tables.Branches.Id.Name}=@id";
DataTable table = _dbContext.GetDataTable(sql, parameters);
if (table != null)
{
return(StudentConverter.TableToStudent(table, includes));
}
return(null);
}
public Students GetPendingStudents(Student.Includes includes)
{
IList <DbParameter> parameters = new List <DbParameter>()
{
new MySqlParameter("@status", "pending"),
new MySqlParameter("@is_active", false)
};
string sql = GetQueryForMultipleTables(includes) + $"LEFT JOIN {Tables.BranchStudents.TableName} ON " +
$" {Tables.Students.TableName}.{Tables.Students.Id.Name} = {Tables.BranchStudents.TableName}.{Tables.BranchStudents.StudentId.Name}" +
$" WHERE {Tables.BranchStudents.TableName}.{Tables.BranchStudents.Status.Name}=@status AND {Tables.BranchStudents.TableName}.{Tables.BranchStudents.IsActive.Name}=@is_active";
DataTable table = _dbContext.GetDataTable(sql, parameters);
if (table != null)
{
return(StudentConverter.TableToStudent(table, includes));
}
return(null);
}
public bool AddStudent(List <Model.Student> list)
{
bool result = false;
try
{
using (var db = new WindowServiceEntities())
{
foreach (var model in list)
{
Db.Student student = new StudentConverter().convertToEntity(model);
db.Students.Add(student);
db.SaveChanges();
result = true;
}
}
}
catch (Exception e)
{
Log.CatchErrors(e);
}
return(result);
}
public Students Contains(Student.Includes includes, string str)
{
IList <DbParameter> parameters = new List <DbParameter>()
{
};
string sql = $"select column_name from INFORMATION_SCHEMA.COLUMNS where table_name = 'students' and column_name not like '%date%'";
DataTable table = _dbContext.GetDataTable(sql, parameters);
List <string> st = new List <string>();
for (int i = 0; i < table.Rows.Count; i++)
{
st.Add(table.Rows[i].ItemArray[0].ToString());
}
string strings = " ";
for (int i = 1; i < table.Rows.Count - 1; i++)
{
strings += " or students." + st[i] + " LIKE '%" + str + "%' ";
}
//if (str == null)
// sql = GetQueryForMultipleTables(includes);
//else
sql = GetQueryForMultipleTables(includes) + $" WHERE {Tables.Students.TableName}." + st[0] + " LIKE '%" + str + "%'" + strings + ";";
table = _dbContext.GetDataTable(sql, parameters);
if (table != null)
{
return(StudentConverter.TableToStudent(table, includes));
}
return(null);
}
public StudentsWebService()
{
_converter = new StudentConverter();
_db = new UnitOfWork(new UniversityContext());
}
public StudentConverterShould()
{
_converter = new StudentConverter();
}
public GroupService(IUnitOfWork uow)
{
_uow = uow;
_converter = new GroupConverter();
_studentConverter = new StudentConverter();
}
public StudentService(IUnitOfWork uow)
{
_uow = uow;
_converter = new StudentConverter();
}
/// <summary>
/// 以前的办法
/// </summary>
public void Test01()
{
Student s = new Student();
Console.WriteLine(StudentConverter.GetSexString(s));
}
