//TODO faculty_short_name и direction_id будут не нужны, если краткое имя профиля уникально. public static string GetProfileName(DB_Connector connection, string facultyShortName, uint directionID, string shortName) { #region Contracts if (connection == null) { throw new ArgumentNullException(nameof(connection)); } if (string.IsNullOrWhiteSpace(facultyShortName)) { throw new ArgumentException("Некорректное краткое имя факультета.", nameof(facultyShortName)); } if (string.IsNullOrWhiteSpace(shortName)) { throw new ArgumentException("Некорректное краткое имя профиля.", nameof(shortName)); } #endregion return(connection.Select( DB_Table.PROFILES, new string[] { "name" }, new List <Tuple <string, Relation, object> > { new Tuple <string, Relation, object>("faculty_short_name", Relation.EQUAL, facultyShortName), new Tuple <string, Relation, object>("direction_id", Relation.EQUAL, directionID), new Tuple <string, Relation, object>("short_name", Relation.EQUAL, shortName) })[0][0].ToString()); }
public static bool ExaminationHasMarks(DB_Connector connection, uint id) { return(connection.Select( DB_Table.ENTRANTS_EXAMINATIONS_MARKS, new string[] { "entrant_id" }, new List <Tuple <string, Relation, object> > { new Tuple <string, Relation, object> ("examination_id", Relation.EQUAL, id) }).Count != 0); }
public static IEnumerable <Mark> GetMarks(DB_Connector connection, IEnumerable <uint> applications, uint campaignID) { #region Contracts if (connection == null) { throw new ArgumentNullException(nameof(connection)); } if (applications == null) { throw new ArgumentNullException(nameof(applications)); } if (applications.Count() == 0) { throw new ArgumentException("Коллекция с заявлениями должена содержать хотя бы один элемент.", nameof(applications)); } #endregion object[] campStartEnd = connection.Select( DB_Table.CAMPAIGNS, new string[] { "start_year", "end_year" }, new List <Tuple <string, Relation, object> > { new Tuple <string, Relation, object>("id", Relation.EQUAL, campaignID) } )[0]; return(applications.Join( connection.Select(DB_Table.APPLICATION_EGE_RESULTS, "application_id", "subject_id", "value", "checked"), k1 => k1, k2 => k2[0], (s1, s2) => new { ApplID = s1, Subj = (uint)s2[1], Mark = (byte)(ushort)s2[2], Checked = (bool)s2[3], ExamDate = (DateTime?)null } ).Concat( applications.Join( connection.Select(DB_Table.APPLICATIONS, "id", "entrant_id"), k1 => k1, k2 => k2[0], (s1, s2) => new { ApplID = s1, EntrID = (uint)s2[1] } ).Join( connection.Select( DB_Table.ENTRANTS_EXAMINATIONS_MARKS, new string[] { "entrant_id", "examination_id", "mark" }, new List <Tuple <string, Relation, object> > { new Tuple <string, Relation, object>("mark", Relation.NOT_EQUAL, -1) } ).Join( connection.Select(DB_Table.EXAMINATIONS, "id", "subject_id", "date") .Where(s => ((DateTime)s[2]).Year == (uint)campStartEnd[0] || ((DateTime)s[2]).Year == (uint)campStartEnd[1]), k1 => k1[1], k2 => k2[0], (s1, s2) => new { EntrID = (uint)s1[0], Subj = (uint)s2[1], Mark = (short)s1[2], Date = (DateTime)s2[2] } ), k1 => k1.EntrID, k2 => k2.EntrID, (s1, s2) => new { s1.ApplID, s2.Subj, Mark = (byte)s2.Mark, Checked = true, ExamDate = (DateTime?)s2.Date } )).Select(s => new Mark(s.ApplID, s.Subj, s.Mark, s.Checked, s.ExamDate))); }
public DB_Helper(DB_Connector connection) { #region Contracts if (connection == null) { throw new System.ArgumentNullException(nameof(connection)); } #endregion _DB_Connection = connection; }
//TODO можно будет объединить с функцией выше, если изменится связь таблицы. public static IEnumerable <uint> GetDirectionEntranceTests(DB_Connector connection, uint campaignID, uint direction) { return(connection.Select( DB_Table.ENTRANCE_TESTS, new string[] { "subject_id" }, new List <Tuple <string, Relation, object> > { new Tuple <string, Relation, object>("campaign_id", Relation.EQUAL, campaignID), new Tuple <string, Relation, object>("direction_id", Relation.EQUAL, direction) }).Select(s => (uint)s[0])); }
public static IEnumerable <Exam> GetCampaignExams(DB_Connector connection, uint campaignID) { Tuple <uint, uint> curCampStartEnd = GetCampaignStartEnd(connection, campaignID); return(connection.Select(DB_Table.EXAMINATIONS, new string[] { "id", "subject_id", "date", "reg_start_date", "reg_end_date" }, new List <Tuple <string, Relation, object> > { new Tuple <string, Relation, object>("date", Relation.GREATER_EQUAL, new DateTime((int)curCampStartEnd.Item1, 1, 1)), new Tuple <string, Relation, object>("date", Relation.LESS_EQUAL, new DateTime((int)curCampStartEnd.Item2, 12, 31)) }).Select(s => new Exam((uint)s[0], (uint)s[1], (DateTime)s[2], (DateTime)s[3], (DateTime)s[4]))); }
public static IEnumerable <Document> GetApplicationDocuments(DB_Connector connection, uint applicationID) { return(connection.CallProcedure("get_application_docs", new Dictionary <string, object> { { "id", applicationID } }).Select(s => new Document( applicationID, (uint)s[0], s[1].ToString(), s[2] as string, s[3] as string, s[4] as DateTime?, s[5] as string, s[6] as DateTime? ))); }
public static ushort GetApplicationIndAchMaxValue(DB_Connector connection, uint applicationID) { IEnumerable <ushort> list = connection.Select(DB_Table.INSTITUTION_ACHIEVEMENTS, "id", "value").Join( connection.Select( DB_Table.INDIVIDUAL_ACHIEVEMENTS, new string[] { "institution_achievement_id" }, new List <Tuple <string, Relation, object> > { new Tuple <string, Relation, object>("application_id", Relation.EQUAL, applicationID) }), k1 => k1[0], k2 => k2[0], (s1, s2) => (ushort)s1[1] ); return(list.Count() != 0 ? list.Max() : (ushort)0); }
public static IEnumerable <Document> GetDocuments(DB_Connector connection, IEnumerable <uint> applications) { return(applications.Join( connection.Select(DB_Table.APPLICATIONS_DOCUMENTS_VIEW), k1 => k1, k2 => k2[0], (s1, s2) => new Document( s1, (uint)s2[1], s2[2].ToString(), s2[3] as string, s2[4] as string, s2[5] as DateTime?, s2[6] as string, s2[7] as DateTime? ))); }
public static Tuple <uint, uint> GetCampaignStartEnd(DB_Connector connection, uint campaignID) { #region Contracts if (connection == null) { throw new ArgumentNullException(nameof(connection)); } #endregion return(connection.Select( DB_Table.CAMPAIGNS, new string[] { "start_year", "end_year" }, new List <Tuple <string, Relation, object> > { new Tuple <string, Relation, object>("id", Relation.EQUAL, campaignID) } ).Select(s => Tuple.Create((uint)s[0], (uint)s[1])).Single()); }