public static void createBenchmark(
Discipline discipline,
int ageFrom,
int ageUntil,
char gender,
string rank,
double benchmark
)
{
executeSql(""
+ "INSERT INTO "
+ "`" + tableName + "` "
+ "("
+ "`" + field_disciplineId + "`, "
+ "`" + field_ageFrom + "`, "
+ "`" + field_ageUntil + "`, "
+ "`" + field_gender + "`, "
+ "`" + field_rank + "`, "
+ "`" + field_benchmark + "`"
+ ") VALUES ("
+ discipline.DisciplineId + ", "
+ ageFrom + ", "
+ ageUntil + ", "
+ "'" + gender + "', "
+ "'" + rank + "', "
+ benchmark
+ ")"
);
}
public static int createVariant(
string variantName,
int ageFrom,
int ageUntil,
Discipline discipline
)
{
executeSql(""
+ "INSERT INTO "
+ "`" + tableName + "` "
+ "("
+ "`" + field_variantName + "`"
+ ") VALUES ("
+ "'" + variantName + "', "
+ ageFrom + ", "
+ ageUntil + ", "
+ discipline.DisciplineId
+ ")"
);
Dictionary<string, object> result = querySingleSql("SELECT MAX(`" + field_variantId + "`) AS `insertionId` FROM `" + tableName + "`");
int insertionId = Convert.ToInt32(result["insertionId"]);
return insertionId;
}
public static void deleteDiscipline(Discipline discipline)
{
executeSql(""
+ "DELETE FROM `" + tableName + "` "
+ "WHERE "
+ "`" + field_disciplineId + " = " + discipline.DisciplineId + " "
+ "LIMIT 1"
);
}
public Subscription(
int sportsFestivalSubscriptionId,
Competition competition,
Discipline discipline,
double result
)
{
this.sportsFestivalSubscriptionId = sportsFestivalSubscriptionId;
this.competition = competition;
this.discipline = discipline;
this.result = result;
}
public Variant(
int variantId,
string variantName,
int ageFrom,
int ageUntil,
Discipline discipline
)
{
this.variantId = variantId;
this.variantName = variantName;
this.ageFrom = ageFrom;
this.ageUntil = ageUntil;
this.discipline = discipline;
}
public static void createSubscription(
int sportsFestivalSubscriptionId,
Competition competition,
Discipline discipline,
double result
)
{
executeSql(""
+ "INSERT INTO "
+ "`" + tableName + "` "
+ "("
+ "`" + field_sportsFestivalSubscriptionId + "`, "
+ "`" + field_competitionId + "`, "
+ "`" + field_disciplineId + "`, "
+ "`" + field_result + "`"
+ ") VALUES ("
+ sportsFestivalSubscriptionId + ", "
+ competition.CompetitionId + ", "
+ discipline.DisciplineId + ", "
+ result
+ ")"
);
}
public static Subscription getSubscriptionByPrimaryKey(
int sportsFestivalSubscriptionId,
Competition competition,
Discipline discipline
)
{
Dictionary<string, object> result = querySingleSql(""
+ "SELECT "
+ "* "
+ "FROM "
+ "`" + tableName + "` "
+ "WHERE "
+ "`" + field_sportsFestivalSubscriptionId + "` = " + sportsFestivalSubscriptionId + " "
+ "AND `" + field_competitionId + "` = " + competition.CompetitionId + " "
+ "AND `" + field_disciplineId + "` = " + discipline.DisciplineId
);
if (result == null)
{
return null;
}
Subscription subscription = new Subscription(
Convert.ToInt32(result[field_sportsFestivalSubscriptionId]),
competition,
discipline,
Convert.ToDouble(result[field_result])
);
return subscription;
}
public static Discipline getDisciplineById(int disciplineId)
{
Dictionary<string, object> result = querySingleSql(""
+ "SELECT "
+ "* "
+ "FROM "
+ "`" + tableName + "` "
+ "WHERE "
+ "`" + field_disciplineId + "` = " + disciplineId
);
if (result == null)
{
return null;
}
Discipline discipline = new Discipline(
Convert.ToInt32(result[field_disciplineId]),
Convert.ToString(result[field_name]),
MeasureProvider.getMeasureById(Convert.ToInt32(result[field_measureId])),
CategoryProvider.getCategoryById(Convert.ToInt32(result[field_categoryId]))
);
foreach (DisciplineSetDisciplineMapping disciplineSetDisciplineMapping in DisciplineSetDisciplineMappingProvider.getDisciplineSetDisciplineMappingsByDiscipline(discipline))
{
discipline.addDisciplineSetDisciplineMapping(disciplineSetDisciplineMapping);
}
List<Variant> variants = VariantProvider.getVariantsByDiscipline(discipline);
foreach (Variant variant in variants)
{
discipline.addVariant(variant);
}
return discipline;
}
public static void updateDiscipline(Discipline discipline)
{
executeSql(""
+ "UPDATE `" + tableName + "` "
+ "SET "
+ "`" + field_name + "` = " + discipline.Name + ", "
+ "`" + field_measureId + "` = " + discipline.Measure.MeasureId + ", "
+ "`" + field_categoryId + "` = " + discipline.Category.CategoryId + " "
+ "WHERE "
+ "`" + field_disciplineId + " = " + discipline.DisciplineId
);
}
public static List<Benchmark> getBenchmarksByDiscipline(Discipline discipline)
{
List<Dictionary<string, object>> results = querySql(""
+ "SELECT "
+ "`" + field_ageFrom + "`, "
+ "`" + field_ageUntil + "`, "
+ "`" + field_gender + "`, "
+ "`" + field_rank + "`, "
+ "`" + field_benchmark + "` "
+ "FROM "
+ "`" + tableName + "` "
+ "WHERE "
+ "`" + field_disciplineId + "` = " + discipline.DisciplineId
);
List<Benchmark> benchmarks = new List<Benchmark>();
foreach (var row in results)
{
Benchmark benchmark = new Benchmark(
discipline,
Convert.ToInt32(row[field_ageFrom]),
Convert.ToInt32(row[field_ageUntil]),
Convert.ToChar(row[field_gender]),
Convert.ToString(row[field_rank]),
Convert.ToDouble(row[field_benchmark])
);
benchmarks.Add(benchmark);
}
return benchmarks;
}
public static Benchmark getBenchmarkByPrimaryKey(
Discipline discipline,
int ageFrom,
int ageUntil,
char gender,
string rank
)
{
Dictionary<string, object> result = querySingleSql(""
+ "SELECT "
+ "`" + field_ageFrom + "`, "
+ "`" + field_ageUntil + "`, "
+ "`" + field_gender + "`, "
+ "`" + field_rank + "`, "
+ "`" + field_benchmark + "` "
+ "FROM "
+ "`" + tableName + "` "
+ "WHERE "
+ "`" + field_disciplineId + "` = " + discipline.DisciplineId + " "
+ "AND `" + field_ageFrom + "` = " + ageFrom + " "
+ "AND `" + field_ageUntil + "` = " + ageUntil + " "
+ "AND `" + field_gender + "` = '" + gender + "' "
+ "AND `" + field_rank + "` = '" + rank + "'"
);
if (result == null)
{
return null;
}
Benchmark benchmark = new Benchmark(
discipline,
Convert.ToInt32(result[field_ageFrom]),
Convert.ToInt32(result[field_ageUntil]),
Convert.ToChar(result[field_gender]),
Convert.ToString(result[field_rank]),
Convert.ToDouble(result[field_benchmark])
);
return benchmark;
}
public static List<Variant> getVariantsByDiscipline(Discipline discipline)
{
List<Dictionary<string, object>> results = querySql(""
+ "SELECT "
+ "* "
+ "FROM "
+ "`" + tableName + "` "
+ "WHERE "
+ "`" + field_disciplineId + "` = " + discipline.DisciplineId
);
List<Variant> variants = new List<Variant>();
foreach (var row in results)
{
variants.Add(
new Variant(
Convert.ToInt32(row[field_variantId]),
Convert.ToString(row[field_variantName]),
Convert.ToInt32(row[field_ageFrom]),
Convert.ToInt32(row[field_ageUntil])
)
);
}
return variants;
}
public static List<DisciplineSet> getDisciplineSetsByDisciplineId(Discipline discipline)
{
return DisciplineSetDisciplineMappingProvider.getDisciplineSetsByDiscipline(discipline);
}
public static bool relationExists(DisciplineSet disciplineSet, Discipline discipline)
{
Dictionary<string, object> result = querySingleSql(""
+ "SELECT "
+ "* "
+ "FROM "
+ "`" + tableName + "` "
+ "WHERE "
+ "`" + field_disciplineSetId + "` = " + disciplineSet.DisciplineSetId + " "
+ "AND `" + field_disciplineId + "` = " + discipline.DisciplineId
);
if (result.Count == 1)
{
return true;
}
return false;
}
public static List<DisciplineSet> getDisciplineSetsByDiscipline(Discipline discipline)
{
List<Dictionary<string, object>> results = querySql(""
+ "SELECT "
+ "* "
+ "FROM "
+ "`" + tableName + "` "
+ "WHERE "
+ "`" + field_disciplineId + "` = " + discipline.DisciplineId
);
List<DisciplineSet> disciplineSets = new List<DisciplineSet>();
foreach (var row in results)
{
disciplineSets.Add(DisciplineSetProvider.getDisciplineSetById(Convert.ToInt32(row[field_disciplineSetId])));
}
return disciplineSets;
}
public static List<DisciplineSetDisciplineMapping> getDisciplineSetDisciplineMappingsByDisciplineSetAndDiscipline(DisciplineSet disciplineSet, Discipline discipline)
{
List<Dictionary<string, object>> results = querySql(""
+ "SELECT "
+ "* "
+ "FROM "
+ "`" + tableName + "` "
+ "WHERE "
+ "`" + field_disciplineSetId + "` = " + disciplineSet.DisciplineSetId + " "
+ "AND `" + field_disciplineId + "` = " + discipline.DisciplineId
);
List<DisciplineSetDisciplineMapping> disciplineSetDisciplineMappings = new List<DisciplineSetDisciplineMapping>();
foreach (var row in results)
{
disciplineSetDisciplineMappings.Add(getDisciplineSetDisciplineMappingByPrimaryKey(
Convert.ToInt32(row[field_disciplineSetId]),
Convert.ToInt32(row[field_ageFrom]),
Convert.ToInt32(row[field_ageUntil]),
Convert.ToInt32(row[field_disciplineId])
));
}
return disciplineSetDisciplineMappings;
}
