public bool DeleteQuestion(Question question)
{
if (databasehandler.DeleteQuestion(question.ID))
return true;
else
return false;
}
protected void btn_AddQuestion_Click(object sender, EventArgs e)
{
if (tbox_AddQuestion.Text.Length > 10 && tbox_AddQuestion.Text.Length < 255)
{
if (Session["currentUser"] != null)
{
Client currentuser = (Client)Session["currentUser"];
string content = tbox_AddQuestion.Text;
int id = currentuser.ClientID;
Question question = new Question(id, content, DateTime.Now, 1);
questionhandler.AddQuestion(question);
Response.Redirect("Client_vragen.aspx");
lbl_ErrorMsg.Visible = false;
}
else
{
lbl_ErrorMsg.Text = "Niet ingelogd als gebruiker";
lbl_ErrorMsg.Visible = true;
}
}
else
{
lbl_ErrorMsg.Text = "Inhoud van de vraag is tekort of te lang";
lbl_ErrorMsg.Visible = true;
}
}
public bool AddQuestion(Question question)
{
if (databasehandler.AddNewQuestion(question))
return true;
else
return false;
}
// Properties
// Methods
public bool DeleteQuestion(Question helprequest)
{
if (questionhandler.DeleteQuestion(helprequest))
{
return true;
} else
{
return false;
}
}
/// <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();
}
}
/// <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();
}
}
/// <summary>
/// Adds a complete question to the database
/// </summary>
/// <param name="newquestion"></param>
/// <returns></returns>
public bool AddQuestion(Question newquestion)
{
try
{
Connect();
cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText =
"Insert into THULPVRAAG(OMSCHRIJVING, LOCATIE, REISTIJD, STARTDATUM, EINDDATUM, URGENT, AANTALVRIJWILLIGERS, VERVOERTYPE, AUTEUR) VALUES (:NewOMSCHRIJVING, :NewLOCATIE, :NewREISTIJD, :NewSTARTDATUM, :NewEINDDATUM, :NewURGENT, :NewAANTALVRIJWILLIGERS, :NewVERVOERTYPE, :NewAUTEUR)";
cmd.Parameters.Add("NewOMSCHRIJVING", OracleDbType.Varchar2).Value = newquestion.Description;
cmd.Parameters.Add("NewLOCATIE", OracleDbType.Varchar2).Value = newquestion.Location;
cmd.Parameters.Add("NewREISTIJD", OracleDbType.Varchar2).Value = newquestion.TravelTime;
cmd.Parameters.Add("NewSTARTDATUM", OracleDbType.Date).Value = newquestion.DateBegin;
cmd.Parameters.Add("NewEINDDATUM", OracleDbType.Date).Value = newquestion.DateEnd;
cmd.Parameters.Add("NewURGENT", OracleDbType.Varchar2).Value = newquestion.Critical;
cmd.Parameters.Add("NewAANTALVRIJWILLIGERS", OracleDbType.Varchar2).Value = newquestion.VolunteersNeeded;
cmd.Parameters.Add("NewVERVOERTYPE", OracleDbType.Varchar2).Value = newquestion.Transport;
cmd.Parameters.Add("NewAUTEUR", newquestion.AuthorID);
cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return false;
}
finally
{
Disconnect();
}
}
public Question ExpandQuestionsWithClient(Question question)
{
return questions.ExpandQuestionWithClient(question);
}
// Methods
public bool UpdateQuestion(Question questiontoupdate)
{
if (questions.UpdateQuestion(questiontoupdate))
return true;
else
return false;
}
/// <summary>
/// Gets a question from the database by authorid and description
/// </summary>
/// <param name="userid"></param>
/// <param name="description"></param>
/// <returns></returns>
public Question GetSingleQuestion(int userid, string description)
{
Question question = null;
try
{
Connect();
cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT * FROM THULPVRAAG H LEFT OUTER JOIN TVERVOER V ON H.VERVOERTYPE = V.ID WHERE H.omschrijving :description AND H.auteur= :userid";
cmd.Parameters.Add("description", description);
cmd.Parameters.Add("userid", userid);
cmd.CommandType = CommandType.Text;
dr = cmd.ExecuteReader();
while (dr.Read())
{
var questionid = SafeReadInt(dr, 0);
var qdescription = SafeReadString(dr, 1);
var location = SafeReadString(dr, 2);
var traveltime = SafeReadString(dr, 3);
var startdate = SafeReadDateTime(dr, 4);
var enddate = SafeReadDateTime(dr, 5);
var critical = SafeReadString(dr, 6);
var volunteers = SafeReadInt(dr, 7);
var clientid = SafeReadInt(dr, 8);
var trash = SafeReadInt(dr, 9);
var transportid = SafeReadInt(dr, 10);
var transportdescription = SafeReadString(dr, 11);
question = new Question(questionid, qdescription, location, traveltime, startdate, enddate, critical, volunteers, clientid, transportid, transportdescription);
}
return question;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
return null;
}
finally
{
Disconnect();
}
}
/// <summary>
/// Gets a question from the database
/// </summary>
/// <param name="questionid">question id of question to get</param>
/// <returns>question if found otherwise null</returns>
public Question GetQuestionByID(int questionid)
{
try
{
Connect();
using (cmd = new OracleCommand())
{
Question returnvalue = null;
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from THULPVRAAG H LEFT OUTER JOIN TVERVOER V ON H.VERVOERTYPE = V.ID WHERE H.ID = :questionID";
cmd.Parameters.Add("questionID", questionid);
dr = cmd.ExecuteReader();
while (dr.Read())
{
var id = SafeReadInt(dr, 0);
var description = SafeReadString(dr, 1);
var location = SafeReadString(dr, 2);
var traveltime = SafeReadString(dr, 3);
var startdate = SafeReadDateTime(dr, 4);
var enddate = SafeReadDateTime(dr, 5);
var critical = SafeReadString(dr, 6);
var volunteers = SafeReadInt(dr, 7);
var clientid = SafeReadInt(dr, 8);
var trash = SafeReadInt(dr, 9);
var transportid = SafeReadInt(dr, 10);
var transportdescription = SafeReadString(dr, 11);
returnvalue = new Question(id, description, location, traveltime, startdate, enddate, critical, volunteers, clientid, transportid, transportdescription);
}
return returnvalue;
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
return null;
}
finally
{
Disconnect();
}
}
/// <summary>
/// Fills the AcceptedBy property of a question
/// </summary>
/// <param name="q"></param>
/// <returns></returns>
public Question ExpandQuestionWithVolunteers(Question q)
{
return databasehandler.ExpandQuestionWithVolunteers(q);
}
/// <summary>
/// Fills the client property of a question
/// </summary>
/// <param name="question"></param>
/// <returns></returns>
public Question ExpandQuestionWithClient(Question question)
{
return databasehandler.AddClientToQuestion(question);
}
/// <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);
}
/// <summary>
/// Updates a question
/// </summary>
/// <param name="question"></param>
/// <returns></returns>
public bool UpdateQuestion(Question question)
{
try
{
Connect();
cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText =
"UPDATE THULPVRAAG SET OMSCHRIJVING = :NewOMSCHRIJVING, LOCATIE = :NewLOCATIE, REISTIJD = :NewREISTIJD, URGENT = :newUrgent, AANTALVRIJWILLIGERS = :newAantalvrijwilligers, VERVOERTYPE = :newVervoertype WHERE ID = :newIDvalue";
cmd.Parameters.Add("NewOMSCHRIJVING", question.Description);
cmd.Parameters.Add("NewLOCATIE", question.Location);
cmd.Parameters.Add("NewREISTIJD", question.TravelTime);
cmd.Parameters.Add("newUrgent", question.Critical.ToString());
cmd.Parameters.Add("newAantalvrijwilligers", question.VolunteersNeeded);
cmd.Parameters.Add("newVervoertype", question.Transport.ID);
cmd.Parameters.Add("newIDvalue", question.ID);
cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return false;
}
finally
{
Disconnect();
}
}
public bool UpdateQuestion(Question questiontoupdate)
{
throw new NotImplementedException();
}
/// <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 AnswerQuestion(Question question, Volunteer volunteer)
{
return questions.AnswerQuestion(question, volunteer);
}
/// <summary>
/// Adds a basic new question to the DB without any extra information
/// </summary>
/// <param name="newquestion"></param>
/// <returns></returns>
public bool AddNewQuestion(Question newquestion)
{
try
{
Connect();
cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "Insert into THULPVRAAG(OMSCHRIJVING,auteur,startdatum,aantalvrijwilligers) VALUES (:NewOMSCHRIJVING,:NewAuteur,:NewStartdatum,:NewAantalvrijwilligers)";
cmd.Parameters.Add("NewOMSCHRIJVING", OracleDbType.Varchar2).Value = newquestion.Description;
cmd.Parameters.Add("NewAuteur", OracleDbType.Int32).Value = newquestion.AuthorID;
cmd.Parameters.Add("NewDatum", OracleDbType.Date).Value = DateTime.Now;
cmd.Parameters.Add("NewAantalvrijwilligers", OracleDbType.Int32).Value = newquestion.VolunteersNeeded;
cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return false;
}
finally
{
Disconnect();
}
}
public Question ExpandQuestionWithVolunteers(Question question)
{
return questions.ExpandQuestionWithVolunteers(question);
}
public bool UpdateQuestion(Question questiontoupdate)
{
if (databasehandler.UpdateQuestion(questiontoupdate))
return true;
else
return false;
}