public Meeting(Client client, Volunteer volunteer, DateTime dateTime, string location)
{
this.Client = client;
this.Volunteer = volunteer;
this.Date = dateTime;
this.Location = location;
}
/// <summary>
/// Buttonclick to register a client
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btn_Register_Client_Click(object sender, EventArgs e)
{
if (CheckUserInput())
{
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 ovpossible = cbox_OVPossible.Checked.ToString();
Client newclient = new Client(username, password, email, givenname, adress, location, phonenumber, haslicense, hascar, ovpossible);
if (loginhandler.AddAccount(newclient))
{
ADMethodsAccountManagement admng = new ADMethodsAccountManagement();
admng.CreateNewUser(username, password, givenname, email);
Response.Redirect("login.aspx");
}
}
}
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> GetClientMeetings(Client client)
{
return dbm.GetMeetings(client);
}
public List<Chat> GetChat(Client client, Volunteer volunteer)
{
return db.GetChat(client, 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();
}
}
/// <summary>
/// Extends a question with additional information about the author
/// </summary>
/// <param name="question"></param>
/// <returns></returns>
public Question AddClientToQuestion(Question question)
{
try
{
Connect();
Client c = 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 H.ID = :clientid";
cmd.Parameters.Add("clientid", question.AuthorID);
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 id = SafeReadInt(dr, 13);
var ovpossible = SafeReadString(dr, 14);
var thrash2 = SafeReadInt(dr, 15);
// Create
c = new Client(accountid, username, password, clientemail, userid, name, adress, location, phonenumber, haslicense, hascar, id, ovpossible, unsubscribedate);
question.Author = c;
}
return question;
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
return null;
}
finally
{
Disconnect();
}
}
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();
}
}
public List<Meeting> GetMyAppointments(Client client)
{
List<Meeting> returnlist = new List<Meeting>();
try
{
Connect();
cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT VOLUNTEER, DATUMTIJD, LOCATIE FROM TAFSPRAAK WHERE CLIENT = :newUSERID";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("newUSERID", OracleDbType.Varchar2).Value = client.UserID;
dr = cmd.ExecuteReader();
while (dr.Read())
{
var volunid = dr.GetInt32(0);
var datetime = dr.GetString(1);
var location = dr.GetString(2);
returnlist.Add(new Meeting(client, GetUserNoConnect(volunid) as Volunteer, Convert.ToDateTime(datetime), location));
}
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();
}
}
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));
}
}
public List<Review> GetMyReviews(Client client)
{
List<Review> returnlist = new List<Review>();
try
{
Connect();
cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT ID, Opmerkingen,beoordeling, FROM TREVIEW WHERE CLIENT ='" + client + "'";
cmd.CommandType = CommandType.Text;
dr = cmd.ExecuteReader();
while (dr.Read())
{
var volunid = dr.GetInt32(0);
var opmerkingen = dr.GetString(1);
var rating = dr.GetInt32(2);
returnlist.Add(new Review(volunid, rating, opmerkingen));
}
return returnlist;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return returnlist;
}
finally
{
Disconnect();
}
}
/// <summary>
/// Gets the meetings for this client
/// </summary>
/// <returns></returns>
public List<Meeting> GetMeetings(Client client)
{
return meetinghandler.GetClientMeetings(client);
}
public List<Question> GetQuestionsByAuthor(Client author)
{
Synchronize();
try
{
return questions.FindAll(x => x.AuthorID == ((Client)author).ClientID);
}
catch (NullReferenceException ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
return null;
}
}
