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 static Students GetStudentsOfBranch(int branchId, Student.Includes includes, int?from, int?amount)
{
try
{
Students students = StudentDataManager.GetStudentsOfBranch(branchId, includes, from, amount);
return(students);
}
catch (Exception ex)
{
_logger.Debug($"Failed to load the students of this branch.", ex);
throw;
}
}
public static Students GetPendingStudents(Student.Includes includes)
{
try
{
Students students = StudentDataManager.GetPendingStudents(includes);
return(students);
}
catch (Exception ex)
{
_logger.Debug($"Failed to load pending students.", ex);
throw;
}
}
public static Student GetStudentByIdentityNumber(int studentIdentityNumber, Student.Includes includes)
{
try
{
Student student = StudentDataManager.GetStudentByIdentityNumber(studentIdentityNumber, includes);
return(student);
}
catch (Exception ex)
{
_logger.Debug($"Failed to load student {studentIdentityNumber}.", ex);
throw;
}
}
public static Students Contains(Student.Includes includes, string str)
{
try
{
Students students = StudentDataManager.Contains(includes, str);
return(students);
}
catch (Exception ex)
{
_logger.Debug($"Failed to load students.", ex);
throw;
}
}
public static Students GetStudents(Student.Includes includes, int?from, int?amount)
{
try
{
Students students = StudentDataManager.GetAllStudents(includes, from, amount);
return(students);
}
catch (Exception ex)
{
_logger.Debug($"Failed to load students.", ex);
throw;
}
}
public static Students TableToStudent(DataTable table, Student.Includes include)
{
if (table == null)
{
return(null);
}
Students students = new Students();
var studentList = table.AsEnumerable().GroupBy(row => DataRowHelper.GetValue <int>(row, Tables.Students.Id.FullName),
(key, group) => RowToStudent(group, include));
students.AddRange(studentList.ToList());
return(students);
}
public static BranchesStudents TableToBranchStudent(DataTable table, Student.Includes includes = Student.Includes.None)
{
if (table == null)
{
return(null);
}
BranchesStudents branchesStudents = new BranchesStudents();
foreach (DataRow row in table.Rows)
{
branchesStudents.Add(RowToBranchStudent(table.AsEnumerable(), includes));
}
return(branchesStudents);
}
private string GetQueryForMultipleTables(Student.Includes includes)
{
string selectStatement = $"SELECT {Tables.BranchStudents.allColumnsAlias} ";
string fromStatement = $" FROM {Tables.BranchStudents.TableName} ";
if (includes.HasFlag(Student.Includes.Branch))
{
selectStatement += $", {Tables.Branches.allColumnsAlias}";
fromStatement += $" LEFT JOIN {Tables.Branches.TableName} ON " +
$"{Tables.Branches.TableName}.{Tables.Branches.Id.Name} = {Tables.BranchStudents.TableName}.{Tables.BranchStudents.BranchId.Name} ";
}
string sql = selectStatement + fromStatement;
return(sql);
}
private string GetQueryForMultipleTables(Student.Includes includes)
{
string selectStatement = $"SELECT {Tables.Students.allColumnsAlias} ";
string fromStatement = $" FROM {Tables.Students.TableName} ";
if (includes.HasFlag(Student.Includes.Bank))
{
selectStatement += $", {Tables.Banks.allColumnsAlias}";
fromStatement += $" LEFT JOIN {Tables.Banks.TableName} ON " +
$"{Tables.Banks.TableName}.{Tables.Banks.Id.Name} = {Tables.Students.TableName}.{Tables.Students.BankId.Name} ";
}
if (includes.HasFlag(Student.Includes.Address))
{
selectStatement += $",{Tables.Address.allColumnsAlias},{Tables.Countries.allColumnsAlias},{Tables.Cities.allColumnsAlias},{Tables.Streets.allColumnsAlias} ";
fromStatement += $" LEFT JOIN {Tables.Address.TableName} ON " +
$" {Tables.Address.TableName}.{Tables.Address.Id.Name} = {Tables.Students.TableName}.{Tables.Students.AddressId.Name}" +
$" LEFT JOIN {Tables.Countries.TableName} ON {Tables.Address.TableName}.{Tables.Address.CountryId.Name}= {Tables.Countries.TableName}.{Tables.Countries.Id.Name} " +
$" LEFT JOIN {Tables.Cities.TableName} ON {Tables.Address.TableName}.{Tables.Address.CityId.Name}={Tables.Cities.TableName}.{Tables.Cities.Id.Name} " +
$" LEFT JOIN {Tables.Streets.TableName} ON {Tables.Address.TableName}.{Tables.Address.StreetId.Name}={Tables.Streets.TableName}.{Tables.Streets.Id.Name} ";
}
if (includes.HasFlag(Student.Includes.Children))
{
selectStatement += $", {Tables.StudentsChildren.allColumnsAlias}";
fromStatement += $" LEFT JOIN {Tables.StudentsChildren.TableName} ON " +
$"{Tables.StudentsChildren.TableName}.{Tables.StudentsChildren.StudentId.Name} = {Tables.Students.TableName}.{Tables.Students.Id.Name} ";
}
if (includes.HasFlag(Student.Includes.BranchStudent))
{
selectStatement += $", {Tables.BranchStudents.allColumnsAlias}";
fromStatement += $" LEFT JOIN {Tables.BranchStudents.TableName} ON " +
$"{Tables.BranchStudents.TableName}.{Tables.BranchStudents.StudentId.Name} = {Tables.Students.TableName}.{Tables.Students.Id.Name} ";
}
if (includes.HasFlag(Student.Includes.Branch))
{
selectStatement += $", {Tables.Branches.allColumnsAlias}";
fromStatement += $" LEFT JOIN {Tables.Branches.TableName} ON " +
$"{Tables.Branches.TableName}.{Tables.Branches.Id.Name} = {Tables.BranchStudents.TableName}.{Tables.BranchStudents.BranchId.Name} ";
}
string sql = selectStatement + fromStatement;
return(sql);
}
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 BranchesStudents GetActiveBranchesOfStudent(int studentId, Student.Includes includes = Student.Includes.None)
{
IList <DbParameter> parameters = new List <DbParameter>()
{
new MySqlParameter("@is_active", true),
new MySqlParameter("@student_id", studentId)
};
string sql = GetQueryForMultipleTables(includes) + $" WHERE {Tables.BranchStudents.TableName}.{Tables.BranchStudents.IsActive.Name}=@is_active AND {Tables.BranchStudents.StudentId.Name}=@student_id ";
DataTable table = _dbContext.GetDataTable(sql, parameters);
if (table != null)
{
return(BranchStudentConverter.TableToBranchStudent(table, includes));
}
return(new BranchesStudents());
}
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 static BranchStudent RowToBranchStudent(IEnumerable <DataRow> rows, Student.Includes includes = Student.Includes.None)
{
DataRow row = rows.FirstOrDefault();
return(new BranchStudent()
{
Id = DataRowHelper.GetValue <int>(row, Tables.BranchStudents.Id.FullName),
EntryGregorianDate = DataRowHelper.GetValue <DateTime>(row, Tables.BranchStudents.EntryGregorianDate.FullName),
EntryHebrewDate = DataRowHelper.GetValue <DateTime>(row, Tables.BranchStudents.EntryHebrewDate.FullName),
Branch = includes.HasFlag(Student.Includes.Branch) ? BranchConverter.RowToBranch(rows, Branch.Includes.Address):null,
ReleaseGregorianDate = DataRowHelper.GetValue <DateTime>(row, Tables.BranchStudents.ReleaseGregorianDate.FullName),
ReleaseHebrewDate = DataRowHelper.GetValue <DateTime>(row, Tables.BranchStudents.ReleaseHebrewDate.FullName),
IsActive = DataRowHelper.GetValue <bool>(row, Tables.BranchStudents.IsActive.FullName),
Student = new Student()
{
Id = DataRowHelper.GetValue <int>(row, Tables.BranchStudents.StudentId.FullName)
},
//Status=(StatusInBranch)Enum.Parse(typeof(StatusInBranch), DataRowHelper.GetValue<string>(row, Tables.BranchStudents.Status.FullName), true),
});
}
public static Student RowToStudent(IEnumerable <DataRow> rows, Student.Includes include)
{
DataRow row = rows.FirstOrDefault();
BranchesStudents branchesStudent = new BranchesStudents();
if (include.HasFlag(Student.Includes.BranchStudent))
{
var studentInstoitutionList = rows.GroupBy(studentRow => DataRowHelper.GetValue <int>(studentRow, Tables.BranchStudents.Id.FullName),
(id, group) => BranchStudentConverter.RowToBranchStudent(group.CopyToDataTable().AsEnumerable(), Student.Includes.Branch)).ToList();
branchesStudent.AddRange(studentInstoitutionList);
}
return(new Student()
{
FirstName = DataRowHelper.GetValue <string>(row, Tables.Students.FirstName.FullName),
LastName = DataRowHelper.GetValue <string>(row, Tables.Students.LastName.FullName),
AccountNumber = DataRowHelper.GetValue <string>(row, Tables.Students.AccountNumber.FullName),
Bank = include.HasFlag(Student.Includes.Bank) ? BankConverter.RowToBank(row) : null,
Address = include.HasFlag(Student.Includes.Address) ? AddressConverter.RowToAddress(row) : null,
BornDate = DataRowHelper.GetValue <DateTime>(row, Tables.Students.BornDate.FullName),
PhoneNumber = DataRowHelper.GetValue <string>(row, Tables.Students.PhoneNumber.FullName),
CellphoneNumber = DataRowHelper.GetValue <string>(row, Tables.Students.CellphoneNumber.FullName),
ChildrenNumber = DataRowHelper.GetValue <int>(row, Tables.Students.ChildrenNumber.FullName),
FaxNumber = DataRowHelper.GetValue <string>(row, Tables.Students.FaxNumber.FullName),
IdCard = DataRowHelper.GetValue <string>(row, Tables.Students.IdCard.FullName),
IdentityNumber = DataRowHelper.GetValue <string>(row, Tables.Students.IdentityNumber.FullName),
Image = DataRowHelper.GetValue <string>(row, Tables.Students.Image.FullName),
JobWife = DataRowHelper.GetValue <string>(row, Tables.Students.JobWife.FullName),
MarriedChildrenNumber = DataRowHelper.GetValue <int>(row, Tables.Students.MarriedChildrenNumber.FullName),
MonthlyIncome = DataRowHelper.GetValue <decimal>(row, Tables.Students.MonthlyIncome.FullName),
Id = DataRowHelper.GetValue <int>(row, Tables.Students.Id.FullName),
TravelExpenses = DataRowHelper.GetValue <decimal>(row, Tables.Students.TravelExpenses.FullName),
WifeName = DataRowHelper.GetValue <string>(row, Tables.Students.WifeName.FullName),
StudentChildren = include.HasFlag(Student.Includes.Children) ? rows.GroupBy(studentRow => DataRowHelper.GetValue <int>(studentRow, Tables.StudentsChildren.Id.FullName),
(id, child) => StudentChildrenConverter.RowToStudentChildren(child.CopyToDataTable().AsEnumerable())).ToList() : null,
StudentBranches = include.HasFlag(Student.Includes.BranchStudent) ? branchesStudent : null,
IsActive = DataRowHelper.GetValue <bool>(row, Tables.Students.IsActive.FullName)
});
}
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);
}
