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