public bool DeleteVolunteer(Volunteer volunteer)
{
if (accounthandler.DeleteAccount(volunteer))
return true;
else
return false;
}
public Meeting(Client client, Volunteer volunteer, DateTime dateTime, string location)
{
this.Client = client;
this.Volunteer = volunteer;
this.Date = dateTime;
this.Location = location;
}
public Chat(string message, DateTime timestamp,Client client, Volunteer volunteer,int sender)
{
this.Message = message;
this.Client = client;
this.Volunteer = volunteer;
this.Sender = sender;
this.TimeStamp = timestamp;
}
public Chat(int id, string message, DateTime timestamp, Client client, Volunteer volunteer)
{
this.ID = id;
this.Message = message;
this.TimeStamp = timestamp;
this.Client = client;
this.Volunteer = volunteer;
}
public string FormattedForClients(Client client, Volunteer volun)
{
if(Sender == 1)
{
return $"{TimeStamp.ToShortTimeString()} - {client.Username}: {Message}";
}
{
return $"{TimeStamp.ToShortTimeString()} - {volun.Username}: {Message}";
}
}
public List<Meeting> GetVolunteerMeetings(Volunteer volunteer)
{
return dbm.GetMeetings(volunteer);
}
public List<Chat> GetChat(Client client, Volunteer volunteer)
{
return db.GetChat(client, volunteer);
}
public List<Meeting> GetMeetings(Volunteer volunteer)
{
return databasehandler.GetMeetings(volunteer);
}
public bool UpdateVolunteer(Volunteer volunteer)
{
return accounthandler.UpdateVolunteer(volunteer);
}
public bool AddMeeting(Volunteer volunteer, DateTime date, string location)
{
throw new NotImplementedException();
}
public Volunteer GetVolunteerDetails(Volunteer volun)
{
try
{
Connect();
cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT * FROM TVRIJWILLIGER WHERE ID = " + volun.UserID;
cmd.CommandType = CommandType.Text;
dr = cmd.ExecuteReader();
while (dr.Read())
{
// Read from DB
int id = dr.GetInt32(0);
DateTime geboortedatum = dr.GetDateTime(1);
var photo = SafeReadString(dr, 2);
var vog = SafeReadString(dr, 3);
var gebruikerid = SafeReadInt(dr, 4);
// Fill
volun.Photo = photo;
volun.VOG = vog;
}
return volun;
}
catch (InvalidCastException ex)
{
MessageBox.Show(ex.ToString());
return null;
}
finally
{
Disconnect();
}
}
//WERKT NOG NIET GOED
public List<Review> GetMyReviews(Volunteer volun)
{
List<Review> returnlist = new List<Review>();
try
{
Connect();
cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT beoordeling, opmerkingen FROM TREVIEW WHERE vrijwilligerid='" + volun.VolunteerID + "'";
cmd.CommandType = CommandType.Text;
dr = cmd.ExecuteReader();
while (dr.Read())
{
var rating = dr.GetInt32(0);
var comment = dr.GetString(1);
returnlist.Add(new Review(rating, comment));
}
return returnlist;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return returnlist;
}
finally
{
Disconnect();
}
}
/// <summary>
/// Gets the meetings of the given user.
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public List<Meeting> GetMeetings(User user)
{
try
{
List<Meeting> meetings = new List<Meeting>();
Connect();
if (user is Client)
{
Client actualuser = user as Client;
using (cmd = new OracleCommand())
{
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from tafspraak a left join tvrijwilliger v on a.VRIJWILLIGERID = v.ID left join tgebruiker g on v.GEBRUIKERID = g.id left join taccount acc on g.ACCOUNTID = acc.ID where hulpbehoevendeid = :clientid";
cmd.Parameters.Add("clientid", actualuser.ClientID);
dr = cmd.ExecuteReader();
while (dr.Read())
{
var meetingid = SafeReadInt(dr, 0);
var clientid = SafeReadInt(dr, 1);
var volunteerid = SafeReadInt(dr, 2);
var date = SafeReadDateTime(dr, 3);
var location = SafeReadString(dr, 4);
var trash = SafeReadInt(dr, 5);
var dateofbirth = SafeReadDateTime(dr, 6);
var profilephoto = SafeReadString(dr, 7);
var vogpath = SafeReadString(dr, 8);
var userid = SafeReadInt(dr, 9);
var trash2 = SafeReadInt(dr, 10);
var givenname = SafeReadString(dr, 11);
var address = SafeReadString(dr, 12);
var city = SafeReadString(dr, 13);
var phonenumber = SafeReadString(dr, 14);
var haslicense = SafeReadString(dr, 15);
var hascar = SafeReadString(dr, 16);
var unsubscribedate = SafeReadDateTime(dr, 17);
var accountid = SafeReadInt(dr, 18);
var thrash3 = SafeReadInt(dr, 19);
var username = SafeReadString(dr, 20);
var password = SafeReadString(dr, 21);
var email = SafeReadString(dr, 22);
Volunteer helper = new Volunteer(accountid, username, password, email, userid, givenname, address, city, phonenumber, haslicense, hascar, unsubscribedate, volunteerid, dateofbirth, profilephoto, vogpath);
Meeting toadd = new Meeting(meetingid, actualuser, helper, date , location);
meetings.Add(toadd);
}
return meetings;
}
}
else
{
Volunteer actualuser = user as Volunteer;
using (cmd = new OracleCommand())
{
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from tafspraak a left join thulpbehoevende h on a.HULPBEHOEVENDEID = h.ID left join tgebruiker g on h.GEBRUIKERID = g.id left join taccount acc on g.ACCOUNTID = acc.ID where a.hulpbehoevendeid = :volunteerid";
cmd.Parameters.Add("volunteerid", actualuser.VolunteerID);
dr = cmd.ExecuteReader();
while (dr.Read())
{
var meetingid = SafeReadInt(dr, 0);
var clientid = SafeReadInt(dr, 1);
var volunteerid = SafeReadInt(dr, 2);
var date = SafeReadDateTime(dr, 3);
var location = SafeReadString(dr, 4);
var trash = SafeReadInt(dr, 5);
var ovpossible = SafeReadString(dr, 6);
var userid = SafeReadInt(dr, 7);
var trash2 = SafeReadInt(dr, 8);
var givenname = SafeReadString(dr, 9);
var address = SafeReadString(dr, 10);
var city = SafeReadString(dr, 11);
var phonenumber = SafeReadString(dr, 12);
var haslicense = SafeReadString(dr, 13);
var hascar = SafeReadString(dr, 14);
var unsubscribedate = SafeReadDateTime(dr, 15);
var accountid = SafeReadInt(dr, 16);
var thrash3 = SafeReadInt(dr, 17);
var username = SafeReadString(dr, 18);
var password = SafeReadString(dr, 19);
var email = SafeReadString(dr, 20);
Client helper = new Client(accountid, username, password, email, userid, givenname, address, city, phonenumber, haslicense, hascar, clientid, ovpossible, unsubscribedate);
Meeting toadd = new Meeting(meetingid, helper, actualuser, date, location);
meetings.Add(toadd);
}
return meetings;
}
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
return null;
}
finally
{
Disconnect();
}
}
public List<Chat> GetChat(Client client, Volunteer volunteer)
{
List<Chat> chatmessages = new List<Chat>();
try
{
Connect();
cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT * FROM(SELECT BERICHT,tijdstip,VANHULPBEHOEVENDE FROM TCHAT WHERE HULPBEHOEVENDEID = :clientid AND VRIJWILLIGERID = :volunteerid ORDER BY TIJDSTIP DESC) WHERE ROWNUM <= 10 ORDER BY ROWNUM DESC";
cmd.Parameters.Add("clientid", client.ClientID);
cmd.Parameters.Add("volunteerid", volunteer.VolunteerID);
cmd.CommandType = CommandType.Text;
dr = cmd.ExecuteReader();
while (dr.Read())
{
var bericht = dr.GetString(0);
var tijdstip = dr.GetDateTime(1);
var sender = dr.GetInt32(2);
chatmessages.Add(new Chat(bericht, tijdstip, sender));
}
return chatmessages;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return null;
}
finally
{
Disconnect();
}
}
public bool UpdateVolunteer(Volunteer volun)
{
throw new NotImplementedException();
}
public bool SendChatMessage(Client client, Volunteer volunteer, string message)
{
try
{
Connect();
cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText =
"Insert into TCHAT(HULPBEHOEVENDEID, VRIJWILLIGERID, BERICHT) VALUES (:NewClient, :NewVolunteer, :NewMessage)";
cmd.Parameters.Add("NewClient", OracleDbType.Int32).Value = client.UserID;
cmd.Parameters.Add("NewVolunteer", OracleDbType.Int32).Value = volunteer.UserID;
cmd.Parameters.Add("NewMessage", OracleDbType.Varchar2).Value = message;
cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return false;
}
finally
{
Disconnect();
}
}
private void RefreshMessages(Client client, Volunteer volunteer)
{
Timer_Refresh.Enabled = true;
lbox_chat.Items.Clear();
List<Chat> chatmessages = new List<Chat>();
chatmessages = chats.GetChat(client, volunteer);
foreach (Chat chatmessage in chatmessages)
{
lbox_chat.Items.Add(chatmessage.FormattedForClients(client, volunteer));
}
}
/// <summary>
/// Adds a volunteer to a question in the database
/// </summary>
/// <param name="q"></param>
/// <param name="v"></param>
/// <returns></returns>
public bool AnswerQuestion(Question q, Volunteer v)
{
try
{
Connect();
using (cmd = new OracleCommand())
{
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO THULPVRAAG_VRIJWILLIGER(HULPVRAAGID, VRIJWILLIGERID) VALUES(:questionid, :volunteerid)";
cmd.Parameters.Add("questionid", q.ID);
cmd.Parameters.Add("volunteerid", v.VolunteerID);
cmd.ExecuteNonQuery();
return true;
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
return false;
}
finally
{
Disconnect();
}
}
public Volunteer ExtendVolunteer(Volunteer volunteer)
{
throw new NotImplementedException();
}
/// <summary>
/// Fills the AcceptedBy property of a question
/// </summary>
/// <param name="q"></param>
/// <returns></returns>
public Question ExpandQuestionWithVolunteers(Question q)
{
try
{
Connect();
Volunteer returnvolunteer = null;
using (cmd = new OracleCommand())
{
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM TACCOUNT A, TGEBRUIKER G, TVRIJWILLIGER V, THULPVRAAG_VRIJWILLIGER HV, THULPVRAAG H WHERE A.ID = G.ACCOUNTID AND G.ID = V.GEBRUIKERID AND V.ID = HV.VRIJWILLIGERID AND HV.HULPVRAAGID = H.ID AND H.ID = :questionid";
cmd.Parameters.Add("questionid", q.ID);
dr = cmd.ExecuteReader();
while (dr.Read())
{
var accountid = SafeReadInt(dr, 0);
var username = SafeReadString(dr, 1);
var password = SafeReadString(dr, 2);
var volunteeremail = SafeReadString(dr, 3);
var userid = SafeReadInt(dr, 4);
var name = SafeReadString(dr, 5);
var adress = SafeReadString(dr, 6);
var location = SafeReadString(dr, 7);
var phonenumber = SafeReadString(dr, 8);
var haslicense = SafeReadString(dr, 9);
var hascar = SafeReadString(dr, 10);
var unsubscribedate = SafeReadDateTime(dr, 11);
var trash1 = SafeReadInt(dr, 12);
var volunteerid = SafeReadInt(dr, 13);
var dateofbirth = SafeReadDateTime(dr, 14);
var pathtophoto = SafeReadString(dr, 15);
var pathtovog = SafeReadString(dr, 16);
var thrash2 = SafeReadInt(dr, 17);
// Create
returnvolunteer = new Volunteer(accountid, username, password, volunteeremail, userid, name, adress, location, phonenumber, haslicense, hascar, unsubscribedate, volunteerid, dateofbirth, pathtophoto, pathtovog);
q.AcceptedBy.Add(returnvolunteer);
}
return q;
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
return null;
}
finally
{
Disconnect();
}
}
public bool AnswerQuestion(Question question, Volunteer volunteer)
{
return questions.AnswerQuestion(question, volunteer);
}
/// <summary>
/// Gets a account from the database
/// </summary>
/// <param name="email">email to login with</param>
/// <returns></returns>
public Account GetAccount(string email)
{
try
{
int someid = -1;
someid = GetClientIdByEmail(email);
if (someid != -1)
{
Connect();
Client returnclient = null;
using (cmd = new OracleCommand())
{
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM TACCOUNT A, TGEBRUIKER G, THULPBEHOEVENDE H WHERE A.ID = G.ACCOUNTID AND H.GEBRUIKERID = G.ID AND LOWER(A.EMAIL) = LOWER(:email)";
cmd.Parameters.Add("email", email);
dr = cmd.ExecuteReader();
while (dr.Read())
{
var accountid = SafeReadInt(dr, 0);
var username = SafeReadString(dr, 1);
var password = SafeReadString(dr, 2);
var clientemail = SafeReadString(dr, 3);
var userid = SafeReadInt(dr, 4);
var name = SafeReadString(dr, 5);
var adress = SafeReadString(dr, 6);
var location = SafeReadString(dr, 7);
var phonenumber = SafeReadString(dr, 8);
var haslicense = SafeReadString(dr, 9);
var hascar = SafeReadString(dr, 10);
var unsubscribedate = SafeReadDateTime(dr, 11);
var trash1 = SafeReadInt(dr, 12);
var clientid = SafeReadInt(dr, 13);
var ovpossible = SafeReadString(dr, 14);
var thrash2 = SafeReadInt(dr, 15);
// Create
returnclient = new Client(accountid, username, password, clientemail, userid, name, adress, location, phonenumber, haslicense, hascar, clientid, ovpossible, unsubscribedate);
}
return returnclient;
}
}
someid = GetVolunteerIdByEmail(email);
if (someid != -1)
{
Connect();
Volunteer returnvolunteer = null;
using (cmd = new OracleCommand())
{
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM TACCOUNT A, TGEBRUIKER G, TVRIJWILLIGER V WHERE A.ID = G.ACCOUNTID AND G.ID = V.GEBRUIKERID AND LOWER(A.EMAIL) = LOWER(:email)";
cmd.Parameters.Add("email", email);
dr = cmd.ExecuteReader();
while (dr.Read())
{
var accountid = SafeReadInt(dr, 0);
var username = SafeReadString(dr, 1);
var password = SafeReadString(dr, 2);
var volunteeremail = SafeReadString(dr, 3);
var userid = SafeReadInt(dr, 4);
var name = SafeReadString(dr, 5);
var adress = SafeReadString(dr, 6);
var location = SafeReadString(dr, 7);
var phonenumber = SafeReadString(dr, 8);
var haslicense = SafeReadString(dr, 9);
var hascar = SafeReadString(dr, 10);
var unsubscribedate = SafeReadDateTime(dr, 11);
var trash1 = SafeReadInt(dr, 12);
var volunteerid = SafeReadInt(dr, 13);
var dateofbirth = SafeReadDateTime(dr, 14);
var pathtophoto = SafeReadString(dr, 15);
var pathtovog = SafeReadString(dr, 16);
var thrash2 = SafeReadInt(dr, 17);
// Create
returnvolunteer = new Volunteer(accountid, username, password, volunteeremail, userid, name, adress, location, phonenumber, haslicense, hascar, unsubscribedate, volunteerid, dateofbirth, pathtophoto, pathtovog);
}
return returnvolunteer;
}
}
someid = GetAdminIdByEmail(email);
if (someid != -1)
{
Connect();
Admin returnadmin = null;
using (cmd = new OracleCommand())
{
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT A.ID, A.GEBRUIKERSNAAM, A.WACHTWOORD, A.EMAIL, B.ID FROM TACCOUNT A, TBEHEERDER B WHERE A.ID = B.ACCOUNTID AND LOWER(A.EMAIL) = LOWER(:email)";
cmd.Parameters.Add("email", email);
dr = cmd.ExecuteReader();
while (dr.Read())
{
var accountid = SafeReadInt(dr, 0);
var username = SafeReadString(dr, 1);
var password = SafeReadString(dr, 2);
var adminemail = SafeReadString(dr, 3);
var adminid = SafeReadInt(dr, 4);
// Create
returnadmin = new Admin(accountid, username, password, adminemail, adminid);
}
return returnadmin;
}
}
else // Nothing found with that email
{
return null;
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
return null;
}
finally
{
Disconnect();
}
}
public Volunteer ExtendVolunteer(Volunteer volunteer)
{
return accounthandler.ExtendVolunteer(volunteer);
}
/// <summary>
/// Button click to register a volunteer
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btn_Register_Volunteer_Click(object sender, EventArgs e)
{
if (CheckUserInput())
{
DateTime dateofbirth;
if (CheckVolunteerInput(out dateofbirth))
{
string username = tbox_Username.Text;
string password = tbox_Password.Text;
string email = tbox_Email.Text;
string givenname = tbox_GivenName.Text;
string adress = tbox_Adress.Text;
string location = tbox_Location.Text;
string phonenumber = tbox_PhoneNumber.Text;
string haslicense = cbox_HasLicense.Checked.ToString();
string hascar = cbox_HasCar.Checked.ToString();
string photofilename = DateTime.Now.ToString("yyyyMMddmmss") + Path.GetFileName(FU_UploadPhoto.FileName);
string vogfilename = DateTime.Now.ToString("yyyyMMddmmss") + Path.GetFileName(FU_UploadVog.FileName);
Volunteer newvolunteer = new Volunteer(username, password, email, givenname, adress, location, phonenumber, haslicense, hascar, dateofbirth, photofilename, vogfilename);
if (loginhandler.AddAccount(newvolunteer))
{
FU_UploadPhoto.SaveAs(Server.MapPath("~/profileimg/") + photofilename);
lbl_UploadPhotoError.Text = "SUCCESS";
FU_UploadVog.SaveAs(Server.MapPath("~/vog/") + vogfilename);
lbl_UploadPhotoError.Text = "SUCCESS";
ADMethodsAccountManagement admng = new ADMethodsAccountManagement();
admng.CreateNewUser(username, password, givenname, email);
Response.Redirect("login.aspx");
}
else
{
}
}
}
}
public List<Review> GetMyReviews(Volunteer volunteer)
{
return databasehandler.GetMyReviews(volunteer);
}
/// <summary>
/// Adds a volunteer to the answered list of a question
/// </summary>
/// <param name="q"></param>
/// <param name="v"></param>
/// <returns></returns>
public bool AnswerQuestion(Question q, Volunteer v)
{
return databasehandler.AnswerQuestion(q, v);
}
