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 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 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);
}