public Branches Contains(Branch.Includes includes, string str)
{
IList <DbParameter> parameters = new List <DbParameter>()
{
};
string sql = $"select column_name from INFORMATION_SCHEMA.COLUMNS where table_name = 'branches' 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 branches." + st[i] + " LIKE '%" + str + "%' ";
}
sql = GetQueryForMultipleTables(includes) + $" WHERE {Tables.Branches.TableName}.{Tables.Branches.Name.Name} LIKE '%" + str + "%';";
table = _dbContext.GetDataTable(sql, parameters);
if (table != null)
{
return(BranchConverter.TableToBranch(table, includes));
}
return(null);
}
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);
}
public static Branches TableToBranch(DataTable table, Branch.Includes includes)
{
if (table == null)
{
return(null);
}
Branches branches = new Branches();
var branchList = table.AsEnumerable().GroupBy(row => DataRowHelper.GetValue <int>(row, Tables.Branches.Id.FullName),
(key, group) => RowToBranch(group, includes));
branches.AddRange(branchList.ToList());
return(branches);
}
public static Branches Contains(Branch.Includes includes, string str)
{
try
{
Branches branches = BranchDataManager.Contains(includes, str);
return(branches);
}
catch (Exception ex)
{
_logger.Debug($"Failed to search branch.", ex);
throw;
}
}
public static Branches GetBranchesBasicDetails(int id, Branch.Includes includes)
{
try
{
Branches branches = BranchDataManager.GetBranchesByType(id, includes);
return(branches);
}
catch (Exception)
{
_logger.Debug($"Failed to get branchs");
throw;
}
}
public Branches GetBranchesByType(int id, Branch.Includes includes)
{
IList <DbParameter> parameters = new List <DbParameter>()
{
new MySqlParameter("@id", id)
};
string sql = GetQueryForMultipleTables(includes) + $" WHERE {Tables.Branches.TypeId.Name}=@id";
DataTable table = _dbContext.GetDataTable(sql, parameters);
if (table != null)
{
return(BranchConverter.TableToBranch(table, includes));
}
return(new Branches());
}
public Branch GetBranchById(int id, Branch.Includes includes)
{
IList <DbParameter> parameters = new List <DbParameter>()
{
new MySqlParameter("@id", id)
};
string sql = GetQueryForMultipleTables(includes) + $" WHERE {Tables.Branches.TableName}.{Tables.Branches.Id.Name}= @id";
DataTable table = _dbContext.GetDataTable(sql, parameters);
if (table != null)
{
return(BranchConverter.RowToBranch(table.AsEnumerable(), includes));
}
return(null);
}
//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
});
}
public static Branch GetBranch(int id, Branch.Includes includes)
{
Branch branch = BranchDataManager.GetBranchById(id, includes);
return(branch);
}
