/// <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);
}
