private string GetQueryForMultipleTables(Branch.Includes includes)
{
string selectStatement = $"SELECT {Tables.Branches.allColumnsAlias} ";
string fromStatement = $" FROM {Tables.Branches.TableName} ";
if (includes.HasFlag(Branch.Includes.Rules))
{
selectStatement += $", {Tables.ExamsRules.allColumnsAlias}";
fromStatement += $" LEFT JOIN {Tables.ExamsRules.TableName} ON " +
$"{Tables.ExamsRules.TableName}.{Tables.ExamsRules.BranchId.Name} = {Tables.Branches.TableName}.{Tables.Branches.Id.Name} ";
selectStatement += $", {Tables.AttendanceRules.allColumnsAlias}";
fromStatement += $" LEFT JOIN {Tables.AttendanceRules.TableName} ON " +
$"{Tables.AttendanceRules.TableName}.{Tables.AttendanceRules.BranchId.Name} = {Tables.Branches.TableName}.{Tables.Branches.Id.Name} ";
}
if (includes.HasFlag(Branch.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.Branches.TableName}.{Tables.Branches.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(Branch.Includes.ActivityHours))
{
selectStatement += $", {Tables.BranchActivityHours.allColumnsAlias}";
fromStatement += $" LEFT JOIN {Tables.BranchActivityHours.TableName} ON " +
$"{Tables.BranchActivityHours.TableName}.{Tables.BranchActivityHours.BranchId.Name} = {Tables.Branches.TableName}.{Tables.Branches.Id.Name} ";
}
if (includes.HasFlag(Branch.Includes.BranchType))
{
selectStatement += $", {Tables.BranchTypes.allColumnsAlias}";
fromStatement += $" LEFT JOIN {Tables.BranchTypes.TableName} ON " +
$"{Tables.BranchTypes.TableName}.{Tables.BranchTypes.Id.Name} = {Tables.Branches.TableName}.{Tables.Branches.TypeId.Name} ";
}
if (includes.HasFlag(Branch.Includes.Institution))
{
selectStatement += $", {Tables.Institutions.allColumnsAlias}";
fromStatement += $" LEFT JOIN {Tables.Institutions.TableName} ON " +
$"{Tables.Institutions.TableName}.{Tables.Institutions.Id.Name} = {Tables.Branches.TableName}.{Tables.Branches.InstitutionId.Name} ";
}
if (includes.HasFlag(Branch.Includes.UserExtraDetails))
{
//include the head
selectStatement += $", {Tables.UserExtraDetails.allColumnsAlias}";
fromStatement += $" LEFT JOIN {Tables.UserExtraDetails.TableName} ON " +
$"{Tables.UserExtraDetails.TableName}.{Tables.UserExtraDetails.UserId.Name} = {Tables.Branches.TableName}.{Tables.Branches.HeadId.Name} ";
}
if (includes.HasFlag(Branch.Includes.Scolarship))
{
//selectStatement += $", {Tables.Scolarships.allColumnsAlias}";
//fromStatement += $" LEFT JOIN {Tables.Scolarships.TableName} ON " +
// $"{Tables.Scolarships.TableName}.{Tables.Scolarships.Id.Name} = {Tables.Branches.TableName}.{Tables.Branches.} ";
}
string sql = selectStatement + fromStatement;
return(sql);
}
//in the middle
public static Branch RowToBranch(IEnumerable <DataRow> rows, Branch.Includes includes)
{
DataRow row = rows.FirstOrDefault();
return(new Branch()
{
Id = DataRowHelper.GetValue <int>(row, Tables.Branches.Id.FullName),
Address = includes.HasFlag(Branch.Includes.Address)?AddressConverter.RowToAddress(row):null,
Head = includes.HasFlag(Branch.Includes.UserExtraDetails) ? UserExtraDetailsConverter.RowToUserExtraDetails(row) : null,
Image = DataRowHelper.GetValue <string>(row, Tables.Branches.Image.FullName),
IsActive = DataRowHelper.GetValue <bool>(row, Tables.Branches.IsActive.FullName),
Name = DataRowHelper.GetValue <string>(row, Tables.Branches.Name.FullName),
OpeningDate = DataRowHelper.GetValue <DateTime>(row, Tables.Branches.OpeningDate.FullName),
StudentsNumber = DataRowHelper.GetValue <int>(row, Tables.Branches.StudentsNumber.FullName),
Type = includes.HasFlag(Branch.Includes.BranchType)?BranchTypeConverter.RowToBranchType(row):null,
StudySubjects = DataRowHelper.GetValue <string>(row, Tables.Branches.StudySubjects.FullName),
ExamRules = includes.HasFlag(Branch.Includes.Rules)?BranchRulesConverter.RowToExamRules(row):null,
AttendanceRules = includes.HasFlag(Branch.Includes.Rules) ? BranchRulesConverter.RowToAttendanceRules(row) : null,
ActivityHours = includes.HasFlag(Branch.Includes.ActivityHours) ? rows.GroupBy(branchRow => DataRowHelper.GetValue <int>(branchRow, Tables.BranchActivityHours.Id.FullName),
(id, hour) => BranchActivityHoursConverter.RowToBranchActivityHours(hour.CopyToDataTable().AsEnumerable())).ToList() : null,
Institution = includes.HasFlag(Branch.Includes.Institution)?InstitutionConverter.RowToInstitution(row):null
});
}
