public static bool DeleteAlbum(Album albumToDelete)
{
bool success = false;
using (CDCatalogEntities db = new CDCatalogEntities())
{
try
{
Album alb = db.Albums.Where(a => a.AlbumID.Equals(albumToDelete.AlbumID)).First();
db.Albums.Remove(alb);
db.SaveChanges();
success = true;
}
catch (Exception SQLe)
{
LogError("CDCatalogManager.DeleteAlbum", SQLe.ToString());
//using (StreamWriter sw = File.AppendText("errorlog.txt"))
//{
// sw.WriteLine("--------------");
// sw.WriteLine(DateTime.Now);
// sw.WriteLine("Source: CDCatalogManager.DeleteAlbums");
// sw.WriteLine(SQLe.ToString());
//}
}
return(success);
}
}
/*
* Gets user type
*/
protected String GetUserType()
{
String user = "";
try
{
SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString); // connection string is in web config
SqlCommand query = new SqlCommand();
sc.Open();
query.Connection = sc;
query.CommandText = "Select UserType From GeneralUser where EmailAddress= '" + (String)Session["UserID"] + "'";// Gathers all notifications for specific user
SqlDataReader read = query.ExecuteReader();
while (read.Read())
{
user = read.GetString(0);
}
sc.Close();
}
catch (SqlException SQLe)
{
System.Diagnostics.Debug.Write(SQLe.ToString());
}
return(user);
}
public static bool DeletePlaylistSong(PlaylistSong playlistSongToDelete)
{
bool success = false;
using (CDCatalogEntities db = new CDCatalogEntities())
{
try
{
db.PlaylistSongs.Remove(playlistSongToDelete);
db.SaveChanges();
success = true;
}
catch (Exception SQLe)
{
LogError("CDCatalogManager.DeletePlaylistSong", SQLe.ToString());
//using (StreamWriter sw = File.AppendText("errorlog.txt"))
//{
// sw.WriteLine("--------------");
// sw.WriteLine(DateTime.Now);
// sw.WriteLine("Source: CDCatalogManager.DeletePlaylistSong");
// sw.WriteLine(SQLe.ToString());
//}
}
return(success);
}
}
public static bool UpdatePlaylistSong(PlaylistSong editedPlaylistSong)
{
bool success = false;
using (CDCatalogEntities db = new CDCatalogEntities())
{
try
{
PlaylistSong playlistSong = db.PlaylistSongs.Where(p => p.PlaylistID.Equals(editedPlaylistSong.PlaylistID) &&
p.SongID.Equals(editedPlaylistSong.SongID)).First();
playlistSong.SongOrder = editedPlaylistSong.SongOrder;
db.SaveChanges();
success = true;
}
catch (Exception SQLe)
{
LogError("CDCatalogManager.UpdatePlaylistSong", SQLe.ToString());
//using (StreamWriter sw = File.AppendText("errorlog.txt"))
//{
// sw.WriteLine("--------------");
// sw.WriteLine(DateTime.Now);
// sw.WriteLine("Source: CDCatalogManager.UpdatePlaylistSong");
// sw.WriteLine(SQLe.ToString());
//}
}
return(success);
}
}
public static bool AddAlbum(Album newAlbum)
{
bool success = false;
using (CDCatalogEntities db = new CDCatalogEntities())
{
try
{
db.Albums.Add(newAlbum);
db.SaveChanges();
success = true;
}
catch (Exception SQLe)
{
LogError("CDCatalogManager.AddAlbum", SQLe.ToString());
//using (StreamWriter sw = File.AppendText("errorlog.txt"))
//{
// sw.WriteLine("--------------");
// sw.WriteLine(DateTime.Now);
// sw.WriteLine("Source: CDCatalogManager.AddAlbum");
// sw.WriteLine(SQLe.ToString());
//}
}
return(success);
}
}
public static List <Song> GetSongsFromPlaylist(Playlist playlist)
{
//Take a Playlist, find all the PlaylistSongs with the matchingPlaylistID,
//and populate a List with all the Songs that match the PlaylistSongs' SongIDs.
List <Song> workingList = GetSongs();
List <Song> songList = new List <Song>();
List <PlaylistSong> pLSList = new List <PlaylistSong>();
using (CDCatalogEntities db = new CDCatalogEntities())
{
try
{
pLSList = db.PlaylistSongs.Where(pls => pls.PlaylistID.Equals(playlist.PlaylistID)).ToList();
foreach (PlaylistSong pls in pLSList)
{
songList.Add(workingList.Where(s => s.SongID.Equals(pls.SongID)).First());
}
}
catch (Exception SQLe)
{
LogError("CDCatalogManager.GetSongsFromPlaylist", SQLe.ToString());
//using (StreamWriter sw = File.AppendText("errorlog.txt"))
//{
// sw.WriteLine("--------------");
// sw.WriteLine(DateTime.Now);
// sw.WriteLine("Source: CDCatalogManager.GetSongsFromPlaylist");
// sw.WriteLine(SQLe.ToString());
//}
}
}
return(songList);
}
public static List <Song> GetSongs()
{
using (CDCatalogEntities db = new CDCatalogEntities())
{
List <Song> songList = new List <Song>();
try
{
songList = db.Songs.OrderByDescending(s => s.Rating).ToList();
//A Song is a complex object. It has associated Artist, Album, and Genre objects,
//and the associated Album object has its own associated Artist object, which may be
//different than the Artist associated with the Song.
//This loop populates the Song object with its necessary associated data.
foreach (Song song in songList)
{
song.Artist = db.Artists.Where(art => art.ArtistID.Equals(song.ArtistID)).First();
song.Genre = db.Genres.Where(g => g.GenreID.Equals(song.GenreID)).First();
song.Album = db.Albums.Where(alb => (alb.AlbumID == song.AlbumID)).First();
song.Album.Artist = db.Artists.Where(art => (art.ArtistID == song.Album.ArtistID)).First();
}
}
catch (Exception SQLe)
{
LogError("CDCatalogManager.GetSongs", SQLe.ToString());
//using (StreamWriter sw = File.AppendText("errorlog.txt"))
//{
// sw.WriteLine("--------------");
// sw.WriteLine(DateTime.Now);
// sw.WriteLine("Source: CDCatalogManager.GetSongs");
// sw.WriteLine(SQLe.ToString());
//}
}
return(songList);
}
}
public static bool UpdateSong(Song editedSong)
{
bool success = false;
using (CDCatalogEntities db = new CDCatalogEntities())
{
try
{
Song son = db.Songs.Where(s => s.SongID.Equals(editedSong.SongID)).First();
son.SongTitle = editedSong.SongTitle;
son.AlbumID = editedSong.AlbumID;
son.ArtistID = editedSong.ArtistID;
son.GenreID = editedSong.GenreID;
son.PlaylistSongs = editedSong.PlaylistSongs;
son.Rating = editedSong.Rating;
son.TrackLength = editedSong.TrackLength;
son.TrackNumber = editedSong.TrackNumber;
db.SaveChanges();
success = true;
}
catch (Exception SQLe)
{
LogError("CDCatalogManager.UpdateSong", SQLe.ToString());
//using (StreamWriter sw = File.AppendText("errorlog.txt"))
//{
// sw.WriteLine("--------------");
// sw.WriteLine(DateTime.Now);
// sw.WriteLine("Source: CDCatalogManager.UpdateSong");
// sw.WriteLine(SQLe.ToString());
//}
}
return(success);
}
}
public static bool UpdatePlaylist(Playlist editedPlaylist)
{
bool success = false;
using (CDCatalogEntities db = new CDCatalogEntities())
{
try
{
Playlist play = db.Playlists.Where(p => p.PlaylistID.Equals(editedPlaylist.PlaylistID)).First();
play.PlaylistName = editedPlaylist.PlaylistName;
//TODO: Need to think about this process some more.
//play.PlaylistSongs = editedPlaylist.PlaylistSongs;
db.SaveChanges();
success = true;
}
catch (Exception SQLe)
{
LogError("CDCatalogManager.UpdatePlaylist", SQLe.ToString());
//using (StreamWriter sw = File.AppendText("errorlog.txt"))
//{
// sw.WriteLine("--------------");
// sw.WriteLine(DateTime.Now);
// sw.WriteLine("Source: CDCatalogManager.UpdatePlaylist");
// sw.WriteLine(SQLe.ToString());
//}
}
return(success);
}
}
public static bool UpdateGenre(Genre editedGenre)
{
bool success = false;
using (CDCatalogEntities db = new CDCatalogEntities())
{
try
{
Genre gen = db.Genres.Where(g => g.GenreID.Equals(editedGenre.GenreID)).First();
gen.GenreName = editedGenre.GenreName;
//gen.Songs = editedGenre.Songs; //This seems unnecessary?
db.SaveChanges();
success = true;
}
catch (Exception SQLe)
{
LogError("CDCatalogManager.UpdateGenre", SQLe.ToString());
//using (StreamWriter sw = File.AppendText("errorlog.txt"))
//{
// sw.WriteLine("--------------");
// sw.WriteLine(DateTime.Now);
// sw.WriteLine("Source: CDCatalogManager.UpdateGenre");
// sw.WriteLine(SQLe.ToString());
//}
}
return(success);
}
}
public static List <Album> GetAlbums()
{
List <Album> albumList = new List <Album>();
using (CDCatalogEntities db = new CDCatalogEntities())
{
try
{
albumList = db.Albums.OrderByDescending(a => a.Rating).ToList();
//An Album object has an associated Artist object; this loop attaches Artists to Albums.
foreach (Album alb in albumList)
{
alb.Artist = db.Artists.Where(art => art.ArtistID.Equals(alb.ArtistID)).First();
}
}
catch (Exception SQLe)
{
LogError("CDCatalogManager.GetAlbums", SQLe.ToString());
//using (StreamWriter sw = File.AppendText("errorlog.txt"))
//{
// sw.WriteLine("--------------");
// sw.WriteLine(DateTime.Now);
// sw.WriteLine("Source: CDCatalogManager.GetAlbums");
// sw.WriteLine(SQLe.ToString());
//}
}
return(albumList);
}
}
protected void InsertParentApplicant(String email)
{
try
{
SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString); // connection string is in web config
SqlCommand query = new SqlCommand();
sc.ConnectionString = "Server=@LOCALHOST; Database = WBLDB; Trusted_Connection = Yes;";
sc.Open();
query.Connection = sc;
query.CommandText = "Insert INTO Applicant(EmailAddress, RequestedAccountType, Approved, DateRequested, DateApproved, StudentInfo)" +
"Values(@EmailAddress, @RequestedAccountType, @Approved, @DateRequested, @DateApproved, @StudentInfo)";
String studentInfo;
studentInfo = "";
studentInfo += txtChildEmail.Text + "," + txtChildFName.Text + "," + txtChildLName.Text + "," + txtChildDOB.Text;
query.Parameters.AddWithValue("@EmailAddress", txtEmail.Text);
query.Parameters.AddWithValue("@RequestedAccountType", Session["userType"].ToString());
query.Parameters.AddWithValue("@Approved", "false");
query.Parameters.AddWithValue("@DateRequested", DateTime.Now);
query.Parameters.AddWithValue("@DateApproved", System.DBNull.Value);
query.Parameters.AddWithValue("@StudentInfo", studentInfo);
query.ExecuteNonQuery();
sc.Close();
}
catch (SqlException SQLe)
{
System.Diagnostics.Debug.Write(SQLe.ToString());
}
}
protected ArrayList GetStudents()
{
ArrayList result = new ArrayList();
String courseID = ddlClasses.SelectedValue.ToString();
try
{
SqlCommand query = new SqlCommand();
SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString);
sc.Open();
query.Connection = sc;
query.CommandText = "SELECT GeneralUser.FirstName, GeneralUser.LastName, GeneralUser.EmailAddress FROM Section " +
"Left JOIN Enrollment Inner JOIN GeneralUser ON Enrollment.EmailAddress = GeneralUser.EmailAddress " +
"ON Enrollment.SectionID = Section.SectionID Where Section.CourseID = " + courseID;
SqlDataReader read = query.ExecuteReader();
while (read.Read())
{
result.Add(read.GetString(0));
result.Add(read.GetString(1));
result.Add(read.GetString(2));
}
sc.Close();
}
catch (SqlException SQLe)
{
System.Diagnostics.Debug.Write(SQLe.ToString());
}
return(result);
}
public static bool UpdateAlbum(Album editedAlbum)
{
bool success = false;
using (CDCatalogEntities db = new CDCatalogEntities())
{
try
{
Album alb = db.Albums.Where(a => a.AlbumID.Equals(editedAlbum.AlbumID)).First();
alb.AlbumTitle = editedAlbum.AlbumTitle;
alb.Rating = editedAlbum.Rating;
alb.Year = editedAlbum.Year;
alb.ArtistID = editedAlbum.ArtistID;
db.SaveChanges();
success = true;
}
catch (Exception SQLe)
{
LogError("CDCatalogManager.UpdateAlbum", SQLe.ToString());
//using (StreamWriter sw = File.AppendText("errorlog.txt"))
//{
// sw.WriteLine("--------------");
// sw.WriteLine(DateTime.Now);
// sw.WriteLine("Source: CDCatalogManager.UpdateAlbum");
// sw.WriteLine(SQLe.ToString());
//}
}
return(success);
}
}
protected ArrayList GatherQuestions()
{
ArrayList result = new ArrayList();
//int count = 0;
try
{
SqlCommand query = new SqlCommand();
SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString);
sc.Open();
query.Connection = sc;
query.CommandText = "Select QuestionID From QuestionOrder Where EvalID = " + (String)Session["EvalID"];// session variable eval
SqlDataReader read = query.ExecuteReader();
while (read.Read())
{
result.Add(read[0]);
System.Diagnostics.Debug.WriteLine(read[0]);
//count++;
}
sc.Close();
}
catch (SqlException SQLe)
{
System.Diagnostics.Debug.Write(SQLe.ToString());
}
return(result);
}
private void insertCipher()
{
try
{
SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString); // connection string is in web config
SqlCommand query = new SqlCommand();
sc.Open();
query.Connection = sc;
query.CommandText = "Insert Into Cipher(EmailAddress, BoolPaid) Values(@EmailAddress, @BoolPaid)";
query.Parameters.AddWithValue("@EmailAddress", txtEmail.Text);
query.Parameters.AddWithValue("@BoolPaid", 1);
Debug.WriteLine(query.CommandText);
Debug.WriteLine("Where @EmailAddress = " + txtEmail.Text);
query.ExecuteNonQuery();
query.CommandText = "update dbo.GeneralUser set ActivatedBool = 1 where EmailAddress = @EmailAddress";
query.Parameters.AddWithValue("@EmailAddress", txtEmail.Text);
query.ExecuteNonQuery();
query.CommandText = "update dbo.GeneralUser set UserPermission = 1 where EmailAddress = @EmailAddress";
query.Parameters.AddWithValue("@EmailAddress", txtEmail.Text);
query.ExecuteNonQuery();
sc.Close();
}
catch (SqlException SQLe)
{
System.Diagnostics.Debug.Write(SQLe.ToString());
}
}
protected String[] GetStudentInfo(String applicantID)
{
String info = "";
try
{
SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString); // connection string is in web config
SqlCommand query = new SqlCommand();
sc.Open();
query.Connection = sc;
query.CommandText = "Select StudentInfo From Applicant Where EmailAddress = @EmailAddress";
query.Parameters.AddWithValue("@EmailAddress", applicantID);
Debug.WriteLine(query.CommandText);
Debug.WriteLine("Where @EmailAddress = " + applicantID);
SqlDataReader read = query.ExecuteReader();
read.Read();
info = read.GetString(0);
sc.Close();
}
catch (SqlException SQLe)
{
System.Diagnostics.Debug.Write(SQLe.ToString());
}
char[] delimiterChars = { ',' };
String[] result = info.Split(delimiterChars);
return(result);
}
protected void AddCipher(String applicantID)
{
try
{
SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString); // connection string is in web config
SqlCommand query = new SqlCommand();
sc.Open();
query.Connection = sc;
query.CommandText = "Insert Into Cipher(EmailAddress, BoolPaid) Values(@EmailAddress, @BoolPaid)";
query.Parameters.AddWithValue("@EmailAddress", applicantID);
query.Parameters.AddWithValue("@BoolPaid", 1);
Debug.WriteLine(query.CommandText);
Debug.WriteLine("Where @EmailAddress = " + applicantID);
query.ExecuteNonQuery();
sc.Close();
}
catch (SqlException SQLe)
{
System.Diagnostics.Debug.Write(SQLe.ToString());
}
}
public static bool UpdateArtist(Artist editedArtist)
{
bool success = false;
using (CDCatalogEntities db = new CDCatalogEntities())
{
try
{
Artist art = db.Artists.Where(a => a.ArtistID.Equals(editedArtist.ArtistID)).First();
art.ArtistName = editedArtist.ArtistName;
//art.Albums = editedArtist.Albums; //This seems unnecessary
//art.Songs = editedArtist.Songs; //This, too.
db.SaveChanges();
success = true;
}
catch (Exception SQLe)
{
LogError("CDCatalogManager.UpdateArtist", SQLe.ToString());
//using (StreamWriter sw = File.AppendText("errorlog.txt"))
//{
// sw.WriteLine("--------------");
// sw.WriteLine(DateTime.Now);
// sw.WriteLine("Source: CDCatalogManager.UpdateArtist");
// sw.WriteLine(SQLe.ToString());
//}
}
return(success);
}
}
protected void AddParent(String applicantID)
{
try
{
SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString); // connection string is in web config
SqlCommand query = new SqlCommand();
sc.Open();
String[] student;
student = GetStudentInfo(applicantID);
//Add Parent Record
query.Connection = sc;
query.CommandText = "Insert Into Parent(EmailAddress, LetterCount, Relationship) Values(@EmailAddress, @LetterCount, @Relationship)";
query.Parameters.AddWithValue("@EmailAddress", applicantID);
query.Parameters.AddWithValue("@LetterCount", 0);
query.Parameters.AddWithValue("@Relationship", student[4]);
Debug.WriteLine(query.CommandText);
Debug.WriteLine("Where @EmailAddress = " + applicantID);
query.ExecuteNonQuery();
// compare student
if (VerifyStudent(student))
{
// add parent student
query.Connection = sc;
query.CommandText = "Insert Into ParentStudent(StudentEmailAddress, ParentEmailAddress, LetterTitel, LetterText, LetterDate) " +
"Values(@StudentEmailAddress, @ParentEmailAddress, @LetterTitel, @LetterText, @LetterDate)";
query.Parameters.AddWithValue("@StudentEmailAddress", student[0]);
query.Parameters.AddWithValue("@ParentEmailAddress", applicantID);
query.Parameters.AddWithValue("@LetterTitel", System.DBNull.Value);
query.Parameters.AddWithValue("@LetterText", System.DBNull.Value);
query.Parameters.AddWithValue("@LetterDate", System.DBNull.Value);
Debug.WriteLine(query.CommandText);
Debug.WriteLine("Where @EmailAddress = " + applicantID);
query.ExecuteNonQuery();
}
sc.Close();
}
catch (SqlException SQLe)
{
System.Diagnostics.Debug.Write(SQLe.ToString());
}
}
public void sendActivationEmail(string email)
{
String accountType = "";
string applicantID = Session["applicantID"].ToString();
try
{
SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString); // connection string is in web config
SqlCommand query = new SqlCommand();
sc.Open();
query.Connection = sc;
query.CommandText = "Select RequestedAccountType From Applicant Where EmailAddress = @EmailAddress";
query.Parameters.AddWithValue("@EmailAddress", applicantID);
Debug.WriteLine(query.CommandText);
Debug.WriteLine("Where @EmailAddress = " + applicantID);
SqlDataReader read = query.ExecuteReader();
while (read.Read())
{
accountType = read.GetString(0);
}
sc.Close();
}
catch (SqlException SQLe)
{
System.Diagnostics.Debug.Write(SQLe.ToString());
}
// Setting up an e-mail message, establishing the credentials for the email address it is coming from and the email address it is going to
MailMessage message = new MailMessage();
SmtpClient client = new SmtpClient();
client.Host = "smtp.gmail.com";
client.Port = 587;
// LOCALHOST NUMBER SHOULD MATCH YOUR BROWSERS
// run any webpage and copy the number over 55866 to debug
string userActivation = "http://*****:*****@gmail.com"); // where activation email is being sent FROM
message.To.Add(email); // where activation email is sent to user-supplied email address.
// TODO: ^^ validate this textbox is in E-mail format
message.Subject = "Account Activation"; // Subject of activation email
message.Body = "Hello! </br></br>Your Words Beats and Life profile has now been approved. Your account activation link is here: </br></br> <a href = '" + userActivation + "'> Activate your account now to join the community!";
message.IsBodyHtml = true; // message contained in html body
client.EnableSsl = true; // secure connection
client.UseDefaultCredentials = true; // have to set up credentials as true
client.Credentials = new System.Net.NetworkCredential("*****@*****.**", "wordsbeatslife"); // user and PW for some client, replace this with user-supplied email/pw
client.Send(message);
}
private void insertStaff()
{
try
{
SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString); // connection string is in web config
SqlCommand query = new SqlCommand();
sc.Open();
query.Connection = sc;
query.CommandText = "Insert Into Staff(EmailAddress, AdminEmailAddress, HireDate, StaffType, Specialty) Values (@EmailAddress, @AdminEmailAddress, @HireDate, @StaffType, @Specialty)";
query.Parameters.AddWithValue("@EmailAddress", txtEmail.Text);
query.Parameters.AddWithValue("@AdminEmailAddress", "*****@*****.**");
query.Parameters.AddWithValue("@StaffType", ddlStaffType.Text);
query.Parameters.AddWithValue("@HireDate", txtHireDate.Text);
query.Parameters.AddWithValue("@Specialty", txtSpecialty.Text);
Debug.WriteLine(query.CommandText);
Debug.WriteLine("Where @EmailAddress = " + txtEmail.Text);
query.ExecuteNonQuery();
query.CommandText = "update dbo.GeneralUser set ActivatedBool = 1 where EmailAddress = @EmailAddress";
query.Parameters.AddWithValue("@EmailAddress", txtEmail.Text);
query.ExecuteNonQuery();
query.CommandText = "update dbo.GeneralUser set UserPermission = 4 where EmailAddress = @EmailAddress";
query.Parameters.AddWithValue("@EmailAddress", txtEmail.Text);
query.ExecuteNonQuery();
query.CommandText = "insert into Evaluatee (EvaluateeEmail, StaffEmailAddress) values (@EmailAddress, @StaffEmailAddress)";
query.Parameters.AddWithValue("@EvaluateeEmail", txtEmail.Text);
query.Parameters.AddWithValue("@StaffEmailAddress", txtEmail.Text);
query.ExecuteNonQuery();
query.CommandText = "insert into Respondent (RespondentEmail, StaffEmailAddress) values (@RespondentEmail, @StaffEmailAddress)";
query.Parameters.AddWithValue("@RespondentEmail", txtEmail.Text);
query.Parameters.AddWithValue("@StaffEmailAddress", txtEmail.Text);
query.ExecuteNonQuery();
sc.Close();
}
catch (SqlException SQLe)
{
System.Diagnostics.Debug.Write(SQLe.ToString());
}
}
protected void PopDropDown()
{
ArrayList courses = new ArrayList();
ArrayList users = new ArrayList();
DateTime date = DateTime.Now;
try
{
SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString); // connection string is in web config
SqlCommand query = new SqlCommand();
sc.Open();
query.Connection = sc;
query.CommandText = "Select Course.CourseName, Enrollment.CourseID from SectionStaff " +
"Inner Join Section on SectionStaff.SectionID = Section.SectionID " +
"Inner Join Enrollment on Section.SectionID = Enrollment.SectionID " +
"Inner Join Course on Enrollment.CourseID = Course.CourseID " +
"Where SectionStaff.EmailAddress = '" + Session["UserID"].ToString() + "'";// Gathers all users of specific type
SqlDataReader read = query.ExecuteReader();
while (read.Read())
{
courses.Add(read.GetString(0));
courses.Add(read.GetInt32(1));
}
sc.Close();
ddlCourses.Items.Insert(0, "Please Select");
for (int i = 0; i < courses.Count; i += 2)
{
ddlCourses.Items.Add(new ListItem(courses[i].ToString(), courses[i + 1].ToString()));
}
}
catch (SqlException SQLe)
{
System.Diagnostics.Debug.Write(SQLe.ToString());
}
}
protected void btnSendAnnounce_Click(object sender, EventArgs e)
{
ArrayList users = new ArrayList();
DateTime date = DateTime.Now;
try
{
SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString); // connection string is in web config
SqlCommand query = new SqlCommand();
sc.Open();
query.Connection = sc;
query.CommandText = "Select EmailAddress From Enrollment Where CourseID = " + ddlCourses.SelectedValue.ToString();// Gathers all users of specific type
SqlDataReader read = query.ExecuteReader();
while (read.Read())
{
users.Add(read.GetString(0));
}
for (int i = 0; i < users.Count; i++)
{
query.CommandText = "Insert into Notifications(EmailAddress, NotificationType, NotificationDate, NotificationText, BriefDescription)" +
" Values(@Email, Announcement, @Date, @Text, @Brief)";
query.Parameters.AddWithValue("@Email", users[i]);
query.Parameters.AddWithValue("@Date", date);
query.Parameters.AddWithValue("@Text", txtText.Text);
query.Parameters.AddWithValue("@Brief", txtHeader.Text);
query.ExecuteNonQuery();
}
sc.Close();
}
catch (SqlException SQLe)
{
System.Diagnostics.Debug.Write(SQLe.ToString());
}
}
protected void PopDropDown()
{
ArrayList result = new ArrayList();
ArrayList courses = new ArrayList();
try
{
SqlCommand query = new SqlCommand();
SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString);
sc.Open();
query.Connection = sc;
query.CommandText = "SELECT Section.CourseID, Course.CourseName FROM SectionStaff " +
"LEFT JOIN Section INNER JOIN Course ON Section.CourseID = Course.CourseID " +
"ON Section.SectionID = SectionStaff.SectionID Where SectionStaff.EmailAddress = '" +
Session["UserID"].ToString() + "'";
SqlDataReader read = query.ExecuteReader();
while (read.Read())
{
result.Add(read.GetInt32(0));
result.Add(read.GetString(1));
}
sc.Close();
ddlClasses.Items.Insert(0, "Please select");
int count = 1;
for (int i = 0; i < result.Count; i += 2)
{
//Debug.WriteLine(i);
ddlClasses.Items.Insert(count, new ListItem(result[i + 1].ToString(), result[i].ToString()));
count++;
}
}
catch (SqlException SQLe)
{
System.Diagnostics.Debug.Write(SQLe.ToString() + "line 84");
}
}
protected void btnSendEmail_Click(object sender, EventArgs e)
{
ArrayList users = new ArrayList();
DateTime startDate = DateTime.Now;
DateTime endDate = DateTime.Now.AddDays(14);
ArrayList events = new ArrayList();
Debug.WriteLine(DateTime.Now);
Debug.WriteLine(DateTime.Now.AddDays(14));
try
{
SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString); // connection string is in web config
SqlCommand query = new SqlCommand();
sc.Open();
query.Connection = sc;
query.CommandText = "Select EmailAddress From " + ddlUser.SelectedValue.ToString();// Gathers all users of specific type
SqlDataReader read = query.ExecuteReader();
while (read.Read())
{
users.Add(read.GetString(0));
}
query.CommandText = "select * from WBLEvent where EventDateTime between '" +
startDate + "' and '" + endDate + "' Order by EventType;";
read = query.ExecuteReader();
sc.Close();
}
catch (SqlException SQLe)
{
System.Diagnostics.Debug.Write(SQLe.ToString());
}
}
protected void GetNotificationData()
{
if (Request.QueryString.Count > 0)
{
if (Request.QueryString.Keys[0] == "NotificationID")
{
try
{
SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString); // connection string is in web config
SqlCommand query = new SqlCommand();
sc.Open();
Debug.WriteLine(Request.QueryString["NotificationID"]);
query.Connection = sc;
query.CommandText = "Select NotificationText, BriefDescription, NotificationDate, NotificationType From Notifications where NotificationID= " + Request.QueryString[0].ToString();// Gathers all notifications for specific user
SqlDataReader read = query.ExecuteReader();
while (read.Read())
{
Debug.WriteLine(read.GetString(0));
description = read.GetString(0);
quick = read.GetString(1);
date = read.GetDateTime(2);
type = read.GetString(3);
}
sc.Close();
}
catch (SqlException SQLe)
{
System.Diagnostics.Debug.Write(SQLe.ToString());
}
}
}
}
private void insertAdmin()
{
try
{
SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString); // connection string is in web config
SqlCommand query = new SqlCommand();
sc.Open();
query.Connection = sc;
query.CommandText = "Insert Into Administrator(EmailAddress, ManagerEmail, AdminTitle, HireDate) Values (@EmailAddress, @ManagerEmail, @AdminTitle, @HireDate)";
query.Parameters.AddWithValue("@EmailAddress", txtEmail.Text);
query.Parameters.AddWithValue("@ManagerEmail", "*****@*****.**");
query.Parameters.AddWithValue("@AdminTitle", txtAdminTitle.Text);
query.Parameters.AddWithValue("@HireDate", txtHireDate.Text);
Debug.WriteLine(query.CommandText);
Debug.WriteLine("Where @EmailAddress = " + txtEmail.Text);
query.ExecuteNonQuery();
query.CommandText = "update dbo.GeneralUser set ActivatedBool = 1 where EmailAddress = @EmailAddress";
query.Parameters.AddWithValue("@EmailAddress", txtEmail.Text);
query.ExecuteNonQuery();
query.CommandText = "update dbo.GeneralUser set UserPermission = 4 where EmailAddress = @EmailAddress";
query.Parameters.AddWithValue("@EmailAddress", txtEmail.Text);
query.ExecuteNonQuery();
sc.Close();
}
catch (SqlException SQLe)
{
System.Diagnostics.Debug.Write(SQLe.ToString());
}
}
public static ArrayList GetProfessors(String studentID)
{
ArrayList studs = new ArrayList();
try
{
SqlConnection sc = new SqlConnection();
SqlCommand query = new SqlCommand();
sc.ConnectionString = @"Server = DESKTOP-QEKTMG0\LOCALHOST; Database = WBLDB; Trusted_Connection = Yes;";
sc.Open();
query.Connection = sc;
query.CommandText = "Select EmailAddress, FirstName, LastName From GeneralUser Where" +
" EmailAddress = (Select EmailAddress From CourseStaff Where CourseID = (Select CourseID " +
"From Course Where CourseID = (Select CourseID From Attendance Where " +
"EmailAddress = @EmailAddress )))";
query.Parameters.AddWithValue("@EmailAddress", studentID);
Debug.WriteLine(query.CommandText);
Debug.WriteLine("Where @EmailAddress = " + studentID);
SqlDataReader read = query.ExecuteReader();
while (read.Read())
{
studs.Add(read.GetString(2));
studs.Add(read.GetString(0));
studs.Add(read.GetString(1));
}
sc.Close();
}
catch (SqlException SQLe)
{
System.Diagnostics.Debug.Write(SQLe.ToString());
}
return(studs);
}
public static ArrayList GetStudents(String parentID)
{
ArrayList studs = new ArrayList();
try
{
SqlConnection sc = new SqlConnection();
SqlCommand query = new SqlCommand();
sc.ConnectionString = @"Server = DESKTOP-QEKTMG0\LOCALHOST; Database = WBLDB; Trusted_Connection = Yes;";
sc.Open();
query.Connection = sc;
query.CommandText = "Select FirstName, LastName, EmailAddress " +
"FROM GeneralUser " +
"Where EmailAddress = (" +
"Select StudentEmailAddress from ParentStudent Where ParentEmailAddress = @ParentEmailAddress)";
query.Parameters.AddWithValue("@ParentEmailAddress", parentID);
Debug.WriteLine(query.CommandText);
Debug.WriteLine("Where @EmailAddress = " + parentID);
SqlDataReader read = query.ExecuteReader();
while (read.Read())
{
studs.Add(read.GetString(2));
studs.Add(read.GetString(0));
studs.Add(read.GetString(1));
}
sc.Close();
}
catch (SqlException SQLe)
{
System.Diagnostics.Debug.Write(SQLe.ToString());
}
return(studs);
}