/// <summary> /// Adds the table. /// </summary> /// <param name="schemaName">Name of the schema.</param> /// <param name="tableName">Name of the table.</param> /// <param name="columns">The columns.</param> /// <exception cref="InvalidOperationException">Table already exists.</exception> public virtual void AddTable(string schemaName, string tableName, params Column[] columns) { if (TableExists(schemaName, tableName)) { var message = Resources.TableAlreadyExists.FormatWith(GetFullTableName(schemaName, tableName)); Logger.Fatal(message); throw new InvalidOperationException(message); } var primaryKeys = GetPrimaryKeys(columns); if (primaryKeys.Count == 0) { var message = Resources.TableDoesntContainPrimaryKey.FormatWith(GetFullTableName(schemaName, tableName)); Logger.Fatal(message); throw new InvalidOperationException(message); } var strs = new List <string>(columns.Length); var columnArray = columns; foreach (var column in columnArray) { if (column.IsPrimaryKey) { column.ColumnProperty = (column.ColumnProperty | ColumnProperty.NotNull) ^ ColumnProperty.PrimaryKey; } strs.Add(GetColumnSql(column, primaryKeys.Count > 1)); } AddTable(schemaName, tableName, strs.ToCommaSeparatedString()); AddPrimaryKey(schemaName, tableName, primaryKeys.ToArray()); }
public WOData(List<Status> StatusList) { using (var db = new mp250dbDB()) { var objStatusArray = StatusList.ToCommaSeparatedString().Split(','); WorkOrders = db.WOes .Where(w => objStatusArray.Contains(w.STATUS + string.Empty)) .ToList(); } }
public void TestToCommaSeparatedListMaxLength() { var test = new List <int>() { 25, 67, 10, 88, 88 }; var r = test.ToCommaSeparatedString(a => a.ToString(), 5); Assert.IsTrue(r == "25,67"); }
public WOData(out List<WODurationCategory> DurationCategories, List<Status> StatusList) { using (var db = new mp250dbDB()) { var objStatusArray = StatusList.ToCommaSeparatedString().Split(','); WorkOrders = db.WOes .Where(w => objStatusArray.Contains(w.STATUS + string.Empty)) .ToList(); } DurationCategories = SegmentByWOType(); }
public AbsenceStatisticsRepository(string ConnectionString) { using (SqlConnection connection = new SqlConnection(ConnectionString)) { // Get all active student IDs List <int> _activeStudentIDs = new List <int>(); using (SqlCommand sqlCommand = new SqlCommand()) { sqlCommand.Connection = connection; sqlCommand.CommandType = CommandType.Text; sqlCommand.CommandText = SQL_ActiveStudentIDs; sqlCommand.Connection.Open(); SqlDataReader dataReader = sqlCommand.ExecuteReader(); if (dataReader.HasRows) { while (dataReader.Read()) { int studentID = Parsers.ParseInt(dataReader["iStudentID"].ToString().Trim()); if (studentID > 0) { if (!_activeStudentIDs.Contains(studentID)) { _activeStudentIDs.Add(studentID); } } } } sqlCommand.Connection.Close(); } using (SqlCommand sqlCommand = new SqlCommand { Connection = connection, CommandType = CommandType.Text, CommandText = SQLQuery + " WHERE Student.iStudentID IN (" + _activeStudentIDs.ToCommaSeparatedString() + ") ORDER BY Student.iStudentID" }) { sqlCommand.Connection.Open(); SqlDataReader dataReader = sqlCommand.ExecuteReader(); if (dataReader.HasRows) { while (dataReader.Read()) { AbsenceStatisticsResponse response = SQLDataReaderToResponse(dataReader); _cache.Add(response.iStudentID, response); } } } } }
public virtual void AddTable(SchemaQualifiedObjectName name, params Column[] columns) { // список колонок, входящих в первичный ключ List <string> pks = columns .Where(column => column.IsPrimaryKey) .Select(column => column.Name) .ToList(); bool compoundPrimaryKey = pks.Count > 1; var querySections = new List <string>(); // SQL для колонок таблицы foreach (Column column in columns) { // Remove the primary key notation if compound primary key because we'll add it back later if (compoundPrimaryKey && column.IsPrimaryKey) { column.ColumnProperty |= ColumnProperty.PrimaryKey; } string columnSql = GetSqlColumnDef(column, compoundPrimaryKey); querySections.Add(columnSql); } // SQL для составного первичного ключа if (compoundPrimaryKey) { string pkName = "PK_" + name.Name; string primaryKeyQuerySection = GetSqlPrimaryKey(pkName, pks); querySections.Add(primaryKeyQuerySection); } string sqlQuerySections = querySections.ToCommaSeparatedString(); string createTableSql = GetSqlAddTable(name, sqlQuerySections); ExecuteNonQuery(createTableSql); }
static void Main(string[] args) { if (args.Any()) { string fileName = string.Empty; string date = string.Empty; List <string> grades = new List <string>() { "pk", "k", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12" }; // There aren't nearly this many blocks in a day, but this future proofs it. It can't pull absences for blocks that dont exist List <int> softBlocks = new List <int>() { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15 }; List <int> hardBlocks = new List <int>() { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15 }; bool allSchools = false; bool onlyPeriodAttendance = false; bool onlyDailyAttendance = false; List <string> options = new List <string>(); List <string> selectedSchoolIDs = new List <string>(); foreach (string argument in args) { if (argument.ToLower().StartsWith("/schoolid:")) { foreach (string enteredID in argument.Substring(10, argument.Length - 10).Split(new char[] { ';', ',' })) { if (!string.IsNullOrEmpty(enteredID)) { selectedSchoolIDs.Add(enteredID); } } } else if (argument.ToLower().StartsWith("/filename:")) { fileName = argument.Substring(10, argument.Length - 10); } else if (argument.ToLower().StartsWith("/allschools")) { options.Add("allschools"); allSchools = true; } else if (argument.ToLower().StartsWith("/justperiodattendance")) { options.Add("justperiodattendance"); onlyPeriodAttendance = true; } else if (argument.ToLower().StartsWith("/justdailyattendance")) { options.Add("justdailyattendance"); onlyDailyAttendance = true; } else if (argument.ToLower().StartsWith("/date:")) { date = argument.Substring(6, argument.Length - 6); if (date.ToLower().Equals("today")) { date = DateTime.Today.ToString(); } if (date.ToLower().Equals("yesterday")) { date = Helpers.GetPreviousBusinessDay(DateTime.Today).ToString(); } if (date.ToLower().Equals("now")) { date = DateTime.Now.ToString(); } } else if (argument.ToLower().StartsWith("/logfilename:")) { Log.LogFileName = argument.Substring(13, argument.Length - 13); } else if (argument.ToLower().StartsWith("/blocks:")) { string blob = argument.Substring(8, argument.Length - 8); char[] splitChars = { ',', ';' }; List <string> unparsedBlocks = blob.Split(splitChars).ToList(); softBlocks.Clear(); foreach (string s in unparsedBlocks) { if (!string.IsNullOrEmpty(s)) { int parsed = 0; if (int.TryParse(s, out parsed)) { if (parsed > 0) { softBlocks.Add(parsed); } } } } } else if (argument.ToLower().StartsWith("/hardblocks:")) { string blob = argument.Substring(12, argument.Length - 12); char[] splitChars = { ',', ';' }; List <string> unparsedBlocks = blob.Split(splitChars).ToList(); hardBlocks.Clear(); foreach (string s in unparsedBlocks) { if (!string.IsNullOrEmpty(s)) { int parsed = 0; if (int.TryParse(s, out parsed)) { if (parsed > 0) { hardBlocks.Add(parsed); } } } } } else if (argument.ToLower().StartsWith("/grades:")) { string gradesRaw = argument.Substring(8, argument.Length - 8).Trim('\"').Trim(); grades = new List <string>(); Log.Info("Limiting student selection to specific grades"); foreach (string ss in gradesRaw.Split(',')) { if (!string.IsNullOrEmpty(ss)) { grades.Add(ss.ToLower()); Log.Info(" Adding grade \"" + ss + "\""); } } } } if (((selectedSchoolIDs.Count <= 0) && (!allSchools)) || (string.IsNullOrEmpty(fileName)) || (string.IsNullOrEmpty(date))) { SendSyntax(); } else { if (Config.ConfigFileExists()) { DateTime parsedDate = Helpers.ParseDate(date); try { Log.ToLog("----------------------------------------------------------------"); Log.Info("Started: " + DateTime.Now); Log.Info("Date: " + date); Log.Info("Output: " + fileName); Log.Info("Grades: " + grades.ToCommaSeparatedString()); Log.Info("Soft Blocks: " + softBlocks.ToCommaSeparatedString()); Log.Info("Hard Blocks: " + hardBlocks.ToCommaSeparatedString()); Log.Info("Options: " + options.ToCommaSeparatedString()); Log.Info("Schools: " + (allSchools ? "ALL" : selectedSchoolIDs.ToCommaSeparatedString())); Dictionary <Student, List <Absence> > studentsWithAbsences = new Dictionary <Student, List <Absence> >(); List <School> selectedSchools = new List <School>(); AbsenceRepository absenceRepo = new AbsenceRepository(); StudentRepository studentRepo = new StudentRepository(); using ( SqlConnection connection = new SqlConnection(Config.dbConnectionString_SchoolLogic)) { SchoolRepository schoolRepo = new SchoolRepository(connection); if (allSchools) { selectedSchools = schoolRepo.GetAll(); } else { selectedSchools = schoolRepo.Get(selectedSchoolIDs); } Log.Info("Loading students"); foreach (School school in selectedSchools) { List <Student> schoolStudents = studentRepo.LoadForSchool(connection, school, parsedDate).Where(s => grades.Contains(s.Grade.ToLower())).ToList(); if (onlyDailyAttendance && !onlyPeriodAttendance) { Log.Info("Only using daily attendance students"); schoolStudents = schoolStudents.Where(s => s.IsTrackDaily == true).ToList(); } if (onlyPeriodAttendance && !onlyDailyAttendance) { Log.Info("Only using period attendance students"); schoolStudents = schoolStudents.Where(s => s.IsTrackDaily == false).ToList(); } Log.Info("Loaded " + schoolStudents.Count + " students for school " + school.Name); int schoolAbsenceCount = 0; // Load student absences foreach (Student student in schoolStudents) { List <Absence> studentAbsences = absenceRepo.LoadAbsencesFor(connection, school, student, parsedDate, parsedDate.AddHours(23.5)).Where(abs => hardBlocks.Contains(abs.BlockNumber)).ToList(); if (studentAbsences.Count > 0) { studentsWithAbsences.Add(student, studentAbsences); } schoolAbsenceCount += studentAbsences.Count; } Log.Info(" Loaded " + schoolAbsenceCount + " absences for school " + school.Name); } } Log.Info("Creating CSV data"); MemoryStream csvContents = AbsenceCSV.GenerateCSV(studentsWithAbsences, softBlocks); Log.Info("Saving CSV file (" + fileName + ")"); if (FileHelpers.FileExists(fileName)) { FileHelpers.DeleteFile(fileName); } FileHelpers.SaveFile(csvContents, fileName); Log.Info("Done!"); } catch (Exception ex) { Log.Error(ex.Message); } } else { Log.Error("Configuration file not found"); Log.Info("Creating new config file (" + Config.configFileName + ")..."); Log.Info("Please edit the file and try again"); Config.CreateNewConfigFile(); } } } else { // If any argument is missing, send the syntax SendSyntax(); } }
static void Main(string[] args) { if (args.Any()) { string fileName = string.Empty; string date = string.Empty; List <string> grades = new List <string>() { "pk", "k", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12" }; bool allSchools = false; List <string> options = new List <string>(); List <string> selectedSchoolIDs = new List <string>(); foreach (string argument in args) { if (argument.ToLower().StartsWith("/schoolid:")) { foreach (string enteredID in argument.Substring(10, argument.Length - 10).Split(new char[] { ';', ',' })) { if (!string.IsNullOrEmpty(enteredID)) { selectedSchoolIDs.Add(enteredID); } } } else if (argument.ToLower().StartsWith("/allschools")) { options.Add("allschools"); allSchools = true; } else if (argument.ToLower().StartsWith("/filename:")) { fileName = argument.Substring(10, argument.Length - 10); } else if (argument.ToLower().StartsWith("/logfilename:")) { Log.LogFileName = argument.Substring(13, argument.Length - 13); } else if (argument.ToLower().StartsWith("/date:")) { date = argument.Substring(6, argument.Length - 6); if (date.ToLower().Equals("today")) { date = DateTime.Today.ToString(); } if (date.ToLower().Equals("yesterday")) { date = Helpers.GetPreviousBusinessDay(DateTime.Today).ToString(); } if (date.ToLower().Equals("now")) { date = DateTime.Now.ToString(); } } else if (argument.ToLower().StartsWith("/grades:")) { string gradesRaw = argument.Substring(8, argument.Length - 8).Trim('\"').Trim(); grades = new List <string>(); Log.ToLog("Limiting student selection to specific grades"); foreach (string ss in gradesRaw.Split(',')) { if (string.IsNullOrEmpty(ss)) { grades.Add(ss.ToLower()); Log.ToLog(" Adding grade \"" + ss + "\""); } } } } if (((selectedSchoolIDs.Count <= 0) && (!allSchools)) || (string.IsNullOrEmpty(fileName)) || (string.IsNullOrEmpty(date))) { SendSyntax(); } else { if (Config.ConfigFileExists()) { DateTime parsedDate = Helpers.ParseDate(date); try { Log.ToLog("----------------------------------------------------------------"); Log.Info("Started: " + DateTime.Now); Log.Info("Date: " + date); Log.Info("Output: " + fileName); Log.Info("Grades: " + grades.ToCommaSeparatedString()); Log.Info("Options: " + options.ToCommaSeparatedString()); Log.Info("Schools: " + (allSchools ? "ALL" : selectedSchoolIDs.ToCommaSeparatedString())); List <Student> reportStudents = new List <Student>(); List <School> selectedSchools = new List <School>(); ContactRepository contactRepo = new ContactRepository(); StudentRepository studentRepo = new StudentRepository(); using (SqlConnection connection = new SqlConnection(Config.dbConnectionString_SchoolLogic)) { SchoolRepository schoolRepo = new SchoolRepository(connection); if (allSchools) { selectedSchools = schoolRepo.GetAll(); } else { selectedSchools = schoolRepo.Get(selectedSchoolIDs); } Log.Info("Loading students"); foreach (School school in selectedSchools) { List <Student> schoolStudents = studentRepo.LoadForSchool(connection, school, parsedDate).Where(s => grades.Contains(s.Grade.ToLower())).ToList(); Log.Info("Loaded " + schoolStudents.Count + " students for school " + school.Name); // Load student contacts Log.Info("Loading student contacts"); foreach (Student student in schoolStudents) { student.Contacts = contactRepo.LoadForStudent(connection, student.DatabaseID); } reportStudents.AddRange(schoolStudents); } } Log.Info("Creating CSV data"); MemoryStream csvContents = AddressBookCSV.GenerateCSV(reportStudents); Log.Info("Saving CSV file (" + fileName + ")"); if (FileHelpers.FileExists(fileName)) { FileHelpers.DeleteFile(fileName); } FileHelpers.SaveFile(csvContents, fileName); Log.Info("Done!"); } catch (Exception ex) { Log.Error(ex.Message); } } else { Log.Error("Configuration file not found"); Log.Info("Creating new config file (" + Config.configFileName + ")..."); Log.Info("Please edit the file and try again"); Config.CreateNewConfigFile(); } } } else { // If any argument is missing, send the syntax SendSyntax(); } }
public ContactRepository(string ConnectionString) { _mailingAddressRepo = new MailingAddressRepository(ConnectionString); // Cache all contacts for all active staff // Get all active student IDs // Get all contact IDs for those student IDs // Load all of those contacts, putting them in collections by student ID // Get contact relations for all contacts using (SqlConnection connection = new SqlConnection(ConnectionString)) { // Get all active student IDs List <int> _activeStudentIDs = new List <int>(); using (SqlCommand sqlCommand = new SqlCommand()) { sqlCommand.Connection = connection; sqlCommand.CommandType = CommandType.Text; sqlCommand.CommandText = SQL_ActiveStudentIDs; sqlCommand.Connection.Open(); SqlDataReader dataReader = sqlCommand.ExecuteReader(); if (dataReader.HasRows) { while (dataReader.Read()) { int studentID = Parsers.ParseInt(dataReader["iStudentID"].ToString().Trim()); if (studentID > 0) { if (!_activeStudentIDs.Contains(studentID)) { _activeStudentIDs.Add(studentID); } } } } sqlCommand.Connection.Close(); } // Get all contact IDs for those student IDs List <int> _activeContactIDs = new List <int>(); using (SqlCommand sqlCommand = new SqlCommand()) { sqlCommand.Connection = connection; sqlCommand.CommandType = CommandType.Text; sqlCommand.CommandText = SQL_ActiveContacts + " WHERE iStudentID IN (" + _activeStudentIDs.ToCommaSeparatedString() + ") ORDER BY iContactID";; sqlCommand.Connection.Open(); SqlDataReader dataReader = sqlCommand.ExecuteReader(); if (dataReader.HasRows) { while (dataReader.Read()) { int contactID = Parsers.ParseInt(dataReader["iContactID"].ToString().Trim()); if (contactID > 0) { if (!_activeContactIDs.Contains(contactID)) { _activeContactIDs.Add(contactID); } } } } sqlCommand.Connection.Close(); } // Load all of those contacts, putting them in collections by student ID _allContacts = new Dictionary <int, Contact>(); using (SqlCommand sqlCommand = new SqlCommand()) { sqlCommand.Connection = connection; sqlCommand.CommandType = CommandType.Text; sqlCommand.CommandText = SQL_Contacts + " WHERE iContactID IN (" + _activeContactIDs.ToCommaSeparatedString() + ") ORDER BY iContactID"; sqlCommand.Connection.Open(); SqlDataReader dataReader = sqlCommand.ExecuteReader(); if (dataReader.HasRows) { while (dataReader.Read()) { int contactID = Parsers.ParseInt(dataReader["iContactID"].ToString().Trim()); if (contactID > 0) { Contact c = dataReaderToContact(dataReader); if (c != null) { if (!_allContacts.ContainsKey(contactID)) { _allContacts.Add(contactID, c); } } } } } sqlCommand.Connection.Close(); } // Get contact relations for all contacts _contactRelationsByStudentID = new Dictionary <int, List <StudentContact> >(); using (SqlCommand sqlCommand = new SqlCommand()) { sqlCommand.Connection = connection; sqlCommand.CommandType = CommandType.Text; sqlCommand.CommandText = SQL_ContactRelations + " WHERE iStudentID IN (" + _activeStudentIDs.ToCommaSeparatedString() + ") ORDER BY iStudentID";;; sqlCommand.Connection.Open(); SqlDataReader dataReader = sqlCommand.ExecuteReader(); if (dataReader.HasRows) { while (dataReader.Read()) { int studentID = Parsers.ParseInt(dataReader["iStudentID"].ToString().Trim()); if (studentID > 0) { StudentContact sc = dataReaderToStudentContact(dataReader); if (sc != null) { if (!_contactRelationsByStudentID.ContainsKey(studentID)) { _contactRelationsByStudentID.Add(studentID, new List <StudentContact>()); } _contactRelationsByStudentID[studentID].Add(sc); } } } } sqlCommand.Connection.Close(); } } }
public static TreeViewItem GetTreeItem(this Film movie) { //Film movie = Context.Films.Where(film => film.Title = movieToRetrieve.Title); TreeViewItem root = new TreeViewItem { Header = movie.Title, Foreground = new SolidColorBrush(Colors.DarkOrange) }; //IsExpanded = true #region QuickInfo TreeViewItem subTreeItem = new TreeViewItem { Foreground = new SolidColorBrush(Colors.DarkOrange), Header = "QuickInfo" }; #region Length to Quickinfo TimeSpan span = TimeSpan.FromMinutes(movie.Length.ToIntSafe()); int hours = ((int)span.TotalHours); int minutes = span.Minutes; string length = (hours >= 1) ? Smart.Format("Length: {0} {0:hour|hours} and {1} {1:minute|minutes}", hours, minutes) : Smart.Format("Length: {1} {1:minute|minutes}", hours, minutes); subTreeItem.Items.Add(new TreeViewItem { Header = length, Foreground = new SolidColorBrush(Colors.DarkOrange) }); #endregion #region Year to QuickInfo subTreeItem.Items.Add(new TreeViewItem { Header = string.Format("Year: {0}", movie.Year), Foreground = new SolidColorBrush(Colors.DarkOrange) }); #endregion #region Rating to QuickInfo subTreeItem.Items.Add(new TreeViewItem { Header = string.Format("Rating: {0}", movie.Rating.MPAARating), Foreground = new SolidColorBrush(Colors.DarkOrange) }); #endregion #region Directors to QuickInfo List <string> directors = movie.PersonFilmIndexes.Where( pfi => pfi.RoleID == FilmRole.DirectorRole.RoleID).Select( director => director.Person.FirstName.ToString(CultureInfo.InvariantCulture) + " " + director.Person.LastName) .ToList(); string directorsAsString = directors.ToCommaSeparatedString().SmartSplit(20).ToList().ToDeliminatedString(Environment.NewLine); TreeViewItem directorRoot = new TreeViewItem { Header = Smart.Format("Director{0:|s}: {1}", directors.Count, directorsAsString), Foreground = new SolidColorBrush(Colors.DarkOrange) }; //directors.ForEach(person => directorRoot.Items.Add(new TreeViewItem { Header = person, Foreground = new SolidColorBrush(Colors.DarkOrange) })); subTreeItem.Items.Add(directorRoot); root.Items.Add(subTreeItem); #endregion #endregion #region Genres subTreeItem = new TreeViewItem { Header = "Genres", Foreground = new SolidColorBrush(Colors.DarkOrange) }; movie.GenreFilmIndexes.Select(gfi => gfi.Genre.Name.ToString(CultureInfo.InvariantCulture)) .ToList().ForEach(genre => subTreeItem.Items.Add(new TreeViewItem { Header = genre, Foreground = new SolidColorBrush(Colors.DarkOrange) })); root.Items.Add(subTreeItem); //tvGenre.Items.Add(treeItem); #endregion #region Actors / Writers subTreeItem = new TreeViewItem { Header = "Actors", Foreground = new SolidColorBrush(Colors.DarkOrange) }; movie.PersonFilmIndexes.Where( pfi => pfi.RoleID == FilmRole.ActorRole.RoleID).Select( pfi => pfi.Person.FirstName.ToString(CultureInfo.InvariantCulture) + " " + pfi.Person.LastName) .ToList().ForEach(person => subTreeItem.Items.Add(new TreeViewItem { Header = person, Foreground = new SolidColorBrush(Colors.DarkOrange) })); root.Items.Add(subTreeItem); subTreeItem = new TreeViewItem { Header = "Writers", Foreground = new SolidColorBrush(Colors.DarkOrange) }; movie.PersonFilmIndexes.Where( pfi => pfi.RoleID == FilmRole.WriterRole.RoleID).Select( pfi => pfi.Person.FirstName.ToString(CultureInfo.InvariantCulture) + " " + pfi.Person.LastName) .ToList().ForEach(person => subTreeItem.Items.Add(new TreeViewItem { Header = person, Foreground = new SolidColorBrush(Colors.DarkOrange) })); root.Items.Add(subTreeItem); //tvCast.Items.Add(treeItem); #endregion #region Plot subTreeItem = new TreeViewItem { IsExpanded = true, Header = "Plot", Foreground = new SolidColorBrush(Colors.DarkOrange) }; string moviePlot = movie.Plot; string plot = string.Empty; if (moviePlot.Length > 40) { List <string> split = moviePlot.SmartSplit(40).ToList(); plot = split.ToDeliminatedString(Environment.NewLine); } subTreeItem.Items.Add(new TreeViewItem { Header = (plot == string.Empty) ? moviePlot : plot, Foreground = new SolidColorBrush(Colors.DarkOrange) }); root.Items.Add(subTreeItem); //tvGenre.Items.Add(treeItem); #endregion return(root); }
public MailingAddressRepository(string ConnectionString) { using (SqlConnection connection = new SqlConnection(ConnectionString)) { // Get all active student IDs List <int> _activeStudentIDs = new List <int>(); using (SqlCommand sqlCommand = new SqlCommand()) { sqlCommand.Connection = connection; sqlCommand.CommandType = CommandType.Text; sqlCommand.CommandText = SQL_ActiveStudentIDs; sqlCommand.Connection.Open(); SqlDataReader dataReader = sqlCommand.ExecuteReader(); if (dataReader.HasRows) { while (dataReader.Read()) { int studentID = Parsers.ParseInt(dataReader["iStudentID"].ToString().Trim()); if (studentID > 0) { if (!_activeStudentIDs.Contains(studentID)) { _activeStudentIDs.Add(studentID); } } } } sqlCommand.Connection.Close(); } // Get all contact IDs for those student IDs List <int> _activeContactIDs = new List <int>(); using (SqlCommand sqlCommand = new SqlCommand()) { sqlCommand.Connection = connection; sqlCommand.CommandType = CommandType.Text; sqlCommand.CommandText = SQL_ActiveContacts + " WHERE iStudentID IN (" + _activeStudentIDs.ToCommaSeparatedString() + ") ORDER BY iContactID";; sqlCommand.Connection.Open(); SqlDataReader dataReader = sqlCommand.ExecuteReader(); if (dataReader.HasRows) { while (dataReader.Read()) { int contactID = Parsers.ParseInt(dataReader["iContactID"].ToString().Trim()); if (contactID > 0) { if (!_activeContactIDs.Contains(contactID)) { _activeContactIDs.Add(contactID); } } } } sqlCommand.Connection.Close(); } // Cache student addresses _cache_Students = new Dictionary <int, List <Address> >(); using (SqlCommand sqlCommand = new SqlCommand()) { sqlCommand.Connection = connection; sqlCommand.CommandType = CommandType.Text; sqlCommand.CommandText = SQL_StudentAddresses + " WHERE iStudentID IN (" + _activeStudentIDs.ToCommaSeparatedString() + ") ORDER BY iStudentID";; sqlCommand.Connection.Open(); SqlDataReader dataReader = sqlCommand.ExecuteReader(); if (dataReader.HasRows) { while (dataReader.Read()) { int id = Parsers.ParseInt(dataReader["iStudentID"].ToString().Trim()); if (id > 0) { Address a = dataReaderToAddress(dataReader); if (a != null) { if (!_cache_Students.ContainsKey(id)) { _cache_Students.Add(id, new List <Address>()); } _cache_Students[id].Add(a); } } } } sqlCommand.Connection.Close(); } // Cache contact addresses _cache_Contacts = new Dictionary <int, List <Address> >(); using (SqlCommand sqlCommand = new SqlCommand()) { sqlCommand.Connection = connection; sqlCommand.CommandType = CommandType.Text; sqlCommand.CommandText = SQL_ContactAddresses + " WHERE iContactID IN (" + _activeContactIDs.ToCommaSeparatedString() + ") ORDER BY iContactID";; sqlCommand.Connection.Open(); SqlDataReader dataReader = sqlCommand.ExecuteReader(); if (dataReader.HasRows) { while (dataReader.Read()) { int id = Parsers.ParseInt(dataReader["iContactID"].ToString().Trim()); if (id > 0) { Address a = dataReaderToAddress(dataReader); if (a != null) { if (!_cache_Contacts.ContainsKey(id)) { _cache_Contacts.Add(id, new List <Address>()); } _cache_Contacts[id].Add(a); } } } } sqlCommand.Connection.Close(); } } }
public virtual void AddTable(SchemaQualifiedObjectName name, params Column[] columns) { // список колонок, вход¤щих в первичный ключ List<string> pks = columns .Where(column => column.IsPrimaryKey) .Select(column => column.Name) .ToList(); bool compoundPrimaryKey = pks.Count > 1; var querySections = new List<string>(); // SQL дл¤ колонок таблицы foreach (Column column in columns) { // Remove the primary key notation if compound primary key because we'll add it back later if (compoundPrimaryKey && column.IsPrimaryKey) { column.ColumnProperty |= ColumnProperty.PrimaryKey; } string columnSql = GetSqlColumnDef(column, compoundPrimaryKey); querySections.Add(columnSql); } // SQL дл¤ составного первичного ключа if (compoundPrimaryKey) { string pkName = "PK_" + name.Name; string primaryKeyQuerySection = GetSqlPrimaryKey(pkName, pks); querySections.Add(primaryKeyQuerySection); } string sqlQuerySections = querySections.ToCommaSeparatedString(); string createTableSql = GetSqlAddTable(name, sqlQuerySections); ExecuteNonQuery(createTableSql); }
public SchoolYearTargetAttendanceChartGenerator(string InternalConnectionString, ChartJob Options) { this.Title = "% Students with at least " + ((decimal)Options.TargetAttendanceRate * 100).ToString("0") + "% Attendance Rate"; this.SubTitle = Options.StartDate.ToShortDateString() + " to " + Options.EndDate.ToShortDateString(); this.ShowValuesInChart = true; // Labels / Data points will be one for each month this.Lines = new List <LineChartLine>(); // DATA NEEDED FOR THIS REPORT // - Load all enrolled students // - For each month within the specified date range, calculate every student's attendance rate // - Find the final values for each month // This chart is done by the School Year, not arbitrary dates InternalStudentRepository studentRepo = new InternalStudentRepository(InternalConnectionString); InternalStudentSchoolEnrolmentRepository schoolEnrolmentRepo = new InternalStudentSchoolEnrolmentRepository(InternalConnectionString); InternalStudentAttendanceRateRepository attendanceRateRepo = new InternalStudentAttendanceRateRepository(InternalConnectionString, Options.StartDate, Options.EndDate); InternalSchoolRepository schoolRepo = new InternalSchoolRepository(InternalConnectionString); // Determine limiting schools (if any) List <int> limitediSchoolIDs = new List <int>(); if (Options.LimitSchools.Count > 0) { limitediSchoolIDs = Options.LimitSchools; List <string> schoolNames = schoolRepo.Get(limitediSchoolIDs).Select(x => x.Name).ToList <string>(); this.SubSubTitle = this.SubTitle; this.SubTitle = schoolNames.ToCommaSeparatedString(); } else { limitediSchoolIDs = schoolRepo.GetAllKnownSchoolIDs(); } LineChartLine fnmStudentsLine = new LineChartLine() { Label = "FNM Students" }; LineChartLine nonFNMStudentsLine = new LineChartLine() { Label = "Non-FNM Students" }; // Find the school year that the specified dates fit into // If the dates span into two or more school years, use the first one detected // The chart will always start at the beginning of the school year and end at the end of the school year int schoolYear = ReturnLower(Parsers.FindSchoolYear(Options.StartDate), Parsers.FindSchoolYear(Options.EndDate)); List <ChartMonth> schoolYears = new List <ChartMonth>() { new ChartMonth { Label = "AUG", Starts = new DateTime(schoolYear, 8, 1), Ends = new DateTime(schoolYear, 8, DateTime.DaysInMonth(schoolYear, 8)) }, new ChartMonth { Label = "SEP", Starts = new DateTime(schoolYear, 9, 1), Ends = new DateTime(schoolYear, 9, DateTime.DaysInMonth(schoolYear, 9)) }, new ChartMonth { Label = "OCT", Starts = new DateTime(schoolYear, 10, 1), Ends = new DateTime(schoolYear, 10, DateTime.DaysInMonth(schoolYear, 10)) }, new ChartMonth { Label = "NOV", Starts = new DateTime(schoolYear, 11, 1), Ends = new DateTime(schoolYear, 11, DateTime.DaysInMonth(schoolYear, 11)) }, new ChartMonth { Label = "DEC", Starts = new DateTime(schoolYear, 12, 1), Ends = new DateTime(schoolYear, 12, DateTime.DaysInMonth(schoolYear, 12)) }, new ChartMonth { Label = "JAN", Starts = new DateTime(schoolYear + 1, 1, 1), Ends = new DateTime(schoolYear + 1, 1, DateTime.DaysInMonth(schoolYear + 1, 1)) }, new ChartMonth { Label = "FEB", Starts = new DateTime(schoolYear + 1, 2, 1), Ends = new DateTime(schoolYear + 1, 2, DateTime.DaysInMonth(schoolYear + 1, 2)) }, new ChartMonth { Label = "MAR", Starts = new DateTime(schoolYear + 1, 3, 1), Ends = new DateTime(schoolYear + 1, 3, DateTime.DaysInMonth(schoolYear + 1, 3)) }, new ChartMonth { Label = "APR", Starts = new DateTime(schoolYear + 1, 4, 1), Ends = new DateTime(schoolYear + 1, 4, DateTime.DaysInMonth(schoolYear + 1, 4)) }, new ChartMonth { Label = "MAY", Starts = new DateTime(schoolYear + 1, 5, 1), Ends = new DateTime(schoolYear + 1, 5, DateTime.DaysInMonth(schoolYear + 1, 5)) }, new ChartMonth { Label = "JUN", Starts = new DateTime(schoolYear + 1, 6, 1), Ends = new DateTime(schoolYear + 1, 6, DateTime.DaysInMonth(schoolYear + 1, 6)) }, new ChartMonth { Label = "JUL", Starts = new DateTime(schoolYear + 1, 7, 1), Ends = new DateTime(schoolYear + 1, 7, DateTime.DaysInMonth(schoolYear + 1, 7)) }, }; this.Labels = schoolYears.Select(x => x.Label).ToList(); foreach (ChartMonth month in schoolYears) { // Check to see if this month falls within the specified time period // If it doesn't, skip it if (month.Starts > Options.EndDate) { continue; } if (month.Ends < Options.StartDate) { continue; } // Clamp the start and end dates to the start and end dates specified if (month.Starts < Options.StartDate) { month.Starts = Options.StartDate; } if (month.Ends > Options.EndDate) { month.Ends = Options.EndDate; } // If the month is the current month, cap the end date at today, because future data won't exist yet if ((month.Starts.Month == DateTime.Today.Month) && (month.Starts.Year == DateTime.Today.Year)) { if (month.Ends > DateTime.Today) { month.Ends = DateTime.Today; } } decimal dataPointThisMonth_NonFNM = (decimal) - 1.0; decimal dataPointThisMonth_FNM = (decimal) - 1.0; // Now, load all the enrolled students during the start and end of the month // and calculate their average attendances List <Student> enrolledStudentsThisMonth = studentRepo.Get(schoolEnrolmentRepo.GetStudentIDsEnrolledOn(month.Starts, month.Ends, limitediSchoolIDs, true)); if (enrolledStudentsThisMonth.Count == 0) { continue; } List <decimal> attendanceRatesNonFNM = new List <decimal>(); List <decimal> attendanceRatesFNM = new List <decimal>(); foreach (Student s in enrolledStudentsThisMonth) { StudentAttendanceRate sar = attendanceRateRepo.GetForStudent(s.iStudentID, month.Starts, month.Ends); decimal attendanceRate = sar.GetAttendanceRate(month.Starts, month.Ends); if (attendanceRate != -1) { if (s.IsFirstNations) { attendanceRatesFNM.Add(attendanceRate); } else { attendanceRatesNonFNM.Add(attendanceRate); } } } // Now, find the number of the rates that are greater than or equal to the target try { dataPointThisMonth_NonFNM = (decimal)((decimal)attendanceRatesNonFNM.Count(x => x > Options.TargetAttendanceRate) / (decimal)attendanceRatesNonFNM.Count()); } catch { } try { dataPointThisMonth_FNM = (decimal)((decimal)attendanceRatesFNM.Count(x => x > Options.TargetAttendanceRate) / (decimal)attendanceRatesFNM.Count()); } catch { } // Add the data point to the line if (dataPointThisMonth_NonFNM > -1) { nonFNMStudentsLine.LineDataPoints.Add(month.Label, dataPointThisMonth_NonFNM); } if (dataPointThisMonth_FNM > -1) { fnmStudentsLine.LineDataPoints.Add(month.Label, dataPointThisMonth_FNM); } } // Finally, add the lines to the chart this.Lines.Add(nonFNMStudentsLine); this.Lines.Add(fnmStudentsLine); }