public Skill(DatabaseQuery query)
{
this.MapleID = query.GetInt("maple_id");
this.Level = query.GetByte("level");
this.MaxLevel = query.GetByte("max_level");
this.Expiration = query.GetDateTime("expiration");
}
// Inherited Functions
public override void Delete()
{
DatabaseQuery query = new DatabaseQuery(Database.Tables.PRESCRIPTION_MEDICATIONS);
DoDelete(query);
PatientsManager.Remove(this);
}
public void WithDatabaseAccessingMEthodCallLoadingVariable(DatabaseEntityVariable <LINQToSQL> dbEntityVariable)
{
var customerEntityDeclaration = new DatabaseEntityDeclaration <LINQToSQL>("Customer");
var orderEntityDeclaration = new DatabaseEntityDeclaration <LINQToSQL>("Order");
var entityDeclarationsUsedInQuery = new List <DatabaseEntityDeclaration <LINQToSQL> >()
{
customerEntityDeclaration
};
DatabaseQueryVariable databaseQueryVariable = null;
var dbQuery = new DatabaseQuery <LINQToSQL>("(from c in dc.Customers where c.Id=1 select c)", entityDeclarationsUsedInQuery, databaseQueryVariable);
var dbAccessingMethodCall = new DatabaseAccessingMethodCallStatement <LINQToSQL>(dbQuery, null);
var entityDeclarationsLoadedByDbCall = new List <DatabaseEntityDeclaration <LINQToSQL> >()
{
customerEntityDeclaration, orderEntityDeclaration
};
dbAccessingMethodCall.SetLoadedEntityDeclarations(entityDeclarationsLoadedByDbCall);
var dbAccessingMethodCallNode = new ORMModelNode(dbAccessingMethodCall);
Tree.RootNode.ChildNodes.Add(dbAccessingMethodCallNode);
}
public override void Load()
{
DatabaseQuery query = new DatabaseQuery(Database.Tables.PERSONAL_DETAILS);
query.Add(Database.Tables.PersonalDetails.FirstName);
query.Add(Database.Tables.PersonalDetails.LastName);
query.Add(Database.Tables.PersonalDetails.Address);
query.Add(Database.Tables.PersonalDetails.PostcodeId);
query.Add(Database.Tables.PersonalDetails.DateOfBirth);
query.Add(Database.Tables.PersonalDetails.Sex);
MySqlDataReader reader = DoLoad(query);
if (Loaded)
{
FirstName = reader.GetString(0);
LastName = reader.GetString(1);
Address = reader.GetString(2);
Postcode = AddressManager.GetPostcode(reader.GetInt32(3));
DateOfBirth = reader.GetDateTime(4);
Sex = reader.GetChar(5);
DetailsManager.Add(this);
}
reader.Close();
Identifications = DetailsManager.GetIdentificationsWithOwner(this);
ContactNumbers = DetailsManager.GetContactNumbersWithOwner(this);
}
public static void HandleWorldSelect(MapleClient client, InPacket iPacket)
{
iPacket.Skip(1);
client.World = iPacket.ReadByte();
client.Channel = iPacket.ReadByte();
byte characterCount = (byte)(long)Database.Scalar("SELECT COUNT(*) FROM `characters` WHERE `account_id` = @account_id", new MySqlParameter("@account_id", client.Account.ID));
using (OutPacket oPacket = new OutPacket(SendOps.SelectWorldResult))
{
oPacket
.WriteBool(false)
.WriteByte(characterCount);
if (characterCount > 0)
{
using (DatabaseQuery query = Database.Query("SELECT * FROM `characters` WHERE `account_id` = @account_id AND `world_id` = @world_id", new MySqlParameter("@account_id", client.Account.ID), new MySqlParameter("world_id", client.World)))
{
while (query.NextRow())
{
LoginHandler.AddCharacterEntry(oPacket, query);
}
}
}
oPacket
.WriteByte(2)
.WriteInt(3); // TODO: Account specific character creation slots. For now, use default 3.
client.Send(oPacket);
}
}
public override void Load()
{
DatabaseQuery query = new DatabaseQuery(Database.Tables.WORKING_DAYS);
query.Add(Database.Tables.WorkingDays.StaffId);
query.Add(Database.Tables.WorkingDays.Sunday);
query.Add(Database.Tables.WorkingDays.Monday);
query.Add(Database.Tables.WorkingDays.Tuesday);
query.Add(Database.Tables.WorkingDays.Wednesday);
query.Add(Database.Tables.WorkingDays.Thursday);
query.Add(Database.Tables.WorkingDays.Friday);
query.Add(Database.Tables.WorkingDays.Saturday);
MySqlDataReader reader = DoLoad(query);
if (Loaded)
{
Owner = StaffsManager.GetMedicalStaff(reader.GetInt32(0));
Sunday = reader.GetByte(1) > 0 ? true : false;
Monday = reader.GetByte(2) > 0 ? true : false;
Tuesday = reader.GetByte(3) > 0 ? true : false;
Wednesday = reader.GetByte(4) > 0 ? true : false;
Thursday = reader.GetByte(5) > 0 ? true : false;
Friday = reader.GetByte(6) > 0 ? true : false;
Saturday = reader.GetByte(7) > 0 ? true : false;
StaffsManager.Add(this);
}
reader.Close();
}
public static List <Prescription> GetPrescriptionsByMedicationName(string name)
{
DatabaseQuery nestedQuery = new DatabaseQuery(Database.Tables.PRESCRIPTION_MEDICATIONS)
{
Comparator = ManagerHelper.GetInLikeComparator
(
Database.Tables.PrescriptionMedications.MedicationId,
Database.Tables.MEDICATIONS,
Database.Tables.Medications.Name,
name
)
};
nestedQuery.Add(Database.Tables.PrescriptionMedications.PrescriptionId);
return(PrescriptionManager.Merge
(
Database.Tables.PRESCRIPTIONS,
ManagerHelper.GetInComparator
(
Database.Tables.Generic.ID,
nestedQuery
)
));
}
// Inherited Functions
public override void Delete()
{
DatabaseQuery query = new DatabaseQuery(Database.Tables.CONTACT_NUMBERS);
DoDelete(query);
DetailsManager.Remove(this);
}
//********************************************************************
// SELECT QUERIES
//********************************************************************
/// <summary>
/// Returns all of the qualifiers Dictionary object
/// </summary>
/// <returns></returns>
public static Dictionary<int, List<List<string>>> GetAllQualifiers()
{
List<string> answer = null;
List<List<string>> temp = null;
Dictionary<int, List<List<string>>> qualifiers = new Dictionary<int, List<List<string>>>();
string queryString = "select Q.Qual_ID, Q.Question, Q.Description, Q.Res_ID, A.Ans_ID, " +
"A.Answer, A.Rank from Qualifiers As Q inner join Answers As A " +
"on Q.Qual_ID = A.Qual_ID";
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Select);
for (int i = 0; i < query.Results.Count; i++) {
answer = new List<string>();
for (int j = 0; j < query.Results[i].Count; j++) {
answer.Add(query.Results[i][j]);
}
if (qualifiers.ContainsKey(Convert.ToInt32(query.Results[i][0]))) {
qualifiers[Convert.ToInt32(query.Results[i][0])].Add(answer);
}
else {
temp = new List<List<string>>();
temp.Add(answer);
qualifiers.Add(Convert.ToInt32(query.Results[i][0]), temp);
}
}
return qualifiers;
}
/// <summary>
/// Reads all the gamers whose gamer tag contains the search request.
/// </summary>
/// <param name="gamerRequest"> String of gamer request </param>
/// <returns> A list of Gamers </returns>
public List <GamerInfo> ReadGamers(string gamerRequest)
{
var DB = new Database();
var DQ = new DatabaseQuery();
var listOfGamers = new List <GamerInfo>();
using (MySqlConnection conn = new MySqlConnection(DB.GetConnString()))
{
string selectQuery = string.Format("SELECT * FROM gamer_info WHERE gamerTag LIKE \'%{0}%\'", gamerRequest);
Console.WriteLine(selectQuery);
MySqlCommand selectCmd = new MySqlCommand(selectQuery, conn);
conn.Open();
using (MySqlDataReader reader = selectCmd.ExecuteReader())
{
while (reader.Read())
{
GamerInfo gamerObj = new GamerInfo();
gamerObj.GamerTag = reader.GetString("gamerTag");
gamerObj.BracketCount = DQ.GetBracketCount(gamerObj.GamerTag);
gamerObj.Region = reader.GetString("region");
listOfGamers.Add(gamerObj);
}
}
}
return(listOfGamers);
}
public User GetUserByEmail(string email)
{
DatabaseQuery databaseQuery = new DatabaseQuery();
User user = databaseQuery.GetUserInfo(email);
return(user);
}
public ExecutionResult SelectData(DatabaseModel select, DatabaseModel condition, List <DatabaseQuery.Condition> conditioners, List <DatabaseQuery> conditionExts = null)
{
DatabaseQuery query = DatabaseQuery.ConstructSelectStatement(select, condition, conditioners, conditionExts);
ExecutionResult result = new ExecutionResult()
{
Stat = ExecutionResult.Status.Success, Data = query.Results
};
List <Dictionary <string, string> > execData = null; string execQuery = query.GetSQLRepresentation();
DatabaseConnection dbConn = DBController.RetrieveDBConnection();
try
{
execData = dbConn.ExecuteQuery(execQuery);
}
catch (DBQExecutionFailException exception)
{
result.Stat = ExecutionResult.Status.Fail;
result.FailInfo = exception.InnerExceptMess;
}
DBController.ReleaseDBConnection(dbConn);
for (int i = 0; i < execData.Count; i++)
{
query.FeedSQLResult(execData.ElementAt(i));
}
return(result);
}
private void OnUpgrading(DatabaseQuery q)
{
if (Upgrading != null)
{
Upgrading(q);
}
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env, ILogger <Startup> logger)
{
_logger = logger;
_query = new DatabaseQuery(_logger);
var resetResult = _query.ResetDatabase();
if (resetResult)
{
_logger.LogInformation("Database successfully reset!");
}
else
{
_logger.LogWarning("Database could not be reset!");
}
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
app.UseCertificateForwarding();
app.UseAuthentication();
app.UseCors("MyPolicy");
app.UsePathBase("/api");
app.UseRouting();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
});
}
/// <summary>
/// Event handler when a participant is finished creating/editing an account.
/// If there are no errors, the session variable will be set to the user and they
/// will be redirected to the participant form.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnParSubmit_Click(object sender, EventArgs e)
{
if (isFormValid(SuperUser.UserType.Participant)) {
DatabaseQuery query;
string queryString = "";
if (Request.QueryString["edit"] == "true") {
int parID = ((Participant)Session["user"]).UserID;
DAL.UpdateParticipant(tbParUserName.Text, tbParFirstName.Text, tbParLastName.Text, tbParEmail.Text, tbParPassword.Text, parID);
}
else {
try {
DAL.InsertParticipant(tbParUserName.Text, tbParFirstName.Text, tbParLastName.Text, tbParEmail.Text, tbParPassword.Text);
}
catch (Exception exception) {
lblParStatus.Text = exception.Message;
lblParStatus.Visible = true;
return;
}
}
lblParStatus.Text = "";
queryString = "select Par_ID from Participant where User_Name = '" + tbParUserName.Text + "'";
query = new DatabaseQuery(queryString, DatabaseQuery.Type.Select);
int userID = Convert.ToInt32(query.Results[0][0]);
Session["user"] = new Participant(userID, tbParUserName.Text, tbParFirstName.Text, tbParLastName.Text, tbParEmail.Text, new List<Answer>());
Response.Redirect("ParticipantForm.aspx");
}
}
public void TestWhere()
{
var query = new DatabaseQuery <TestEntity>(new DummyProcessor());
query.Where(e => e["Age"].Value <int>() < 4 && e["Age"].Value <int>() > 0);
var result = query.GetResult();
Assert.AreEqual(3, result.Count);
for (int i = 1; i < 4; i++)
{
Assert.IsTrue(result.MoveNext());
AssertEntity(result.Current, i);
}
// Test with non-indexed key but using Where() method.
query = new DatabaseQuery <TestEntity>(new DummyProcessor());
query.Where(e => e["LastName"].ToString().Equals("LN0"));
try
{
result = query.GetResult();
Assert.Fail("There should've been an exception!");
}
catch (Exception) {}
}
private DatabaseQuery CreateQuery(bool orderResults)
{
var query = new DatabaseQuery();
// Select a few columns.
query.SelectField(Monikers.FlightId);
query.SelectField(Monikers.TailNumber);
query.SelectField(Monikers.CityPair);
query.SelectField(Monikers.TakeoffAirportName);
query.SelectField(Monikers.LandingAirportName);
// Filter for takeoff and landing valid.
query.AddBooleanFilter(Monikers.TakeoffValid, true);
query.AddBooleanFilter(Monikers.LandingValid, true);
// Order by flight id.
if (orderResults)
{
query.OrderByField(Monikers.FlightId);
}
// Use display formatting.
query.ValueFormat = DbQueryFormat.Display;
return(query);
}
/// <summary>
/// Inserts gamer into bracket
/// </summary>
///
/// <param name="gamer">
/// Gamer object to be inserted
/// </param>
///
/// <param name="bracketID">
/// BracketID associated with bracket, where the gamer will be inserted
/// </param>
///
/// <returns>
/// BracketPlayer object if insertion successful; null if not
/// </returns>
public BracketPlayer InsertGamerToBracket(GamerInfo gamer, BracketInfo bracket)
{
try
{
DatabaseQuery databaseQuery = new DatabaseQuery();
TournamentBracketDatabaseQuery tournamentBracketDatabaseQuery = new TournamentBracketDatabaseQuery();
GamerInfo tempGamer = databaseQuery.GetGamerInfo(gamer);
BracketPlayer bracketPlayer = new BracketPlayer();
bracketPlayer.BracketID = bracket.BracketID;
bracketPlayer.HashedUserID = tempGamer.HashedUserID;
bracketPlayer.StatusCode = 1;
bracketPlayer.Claim = null;
bracketPlayer.Reason = null;
bool insertionResult = tournamentBracketDatabaseQuery.InsertBracketPlayer(bracketPlayer);
if (insertionResult)
{
tournamentBracketDatabaseQuery.UpdateBracketPlayerCount(bracket.BracketID, 1);
return(bracketPlayer);
}
return(null);
}
catch (Exception e)
{
Console.WriteLine(e);
return(null);
}
}
// Inherited Functions
public override void Delete()
{
DatabaseQuery query = new DatabaseQuery(Database.Tables.IDENTIFICATIONS);
DoDelete(query);
DetailsManager.Remove(this);
}
public static List <Patient> GetPatientsByPostcode(string code)
{
DatabaseQuery nestedQuery = new DatabaseQuery(Database.Tables.PERSONAL_DETAILS)
{
Comparator = new QueryComparator()
{
Source = new QueryElement(Database.Tables.PersonalDetails.PostcodeId),
Operand = new QueryElement
(
ManagerHelper.GetInEqualQuery
(
Database.Tables.POSTAL_CODES,
Database.Tables.PostalCodes.Code,
code
)
)
}
};
nestedQuery.Add(Database.Tables.Generic.ID);
return(PatientManager.Merge
(
Database.Tables.PATIENTS,
ManagerHelper.GetInComparator
(
Database.Tables.Patients.DetailsId,
nestedQuery
)
));
}
// Inherited Functions
public override void Delete()
{
DatabaseQuery query = new DatabaseQuery(Database.Tables.APPOINTMENTS);
DoDelete(query);
PatientsManager.Remove(this);
}
public void DatabaseQuery_Parameters_Empty_Success()
{
var query = new DatabaseQuery();
Assert.IsNotNull(query.Parameters);
Assert.AreEqual(0, query.Parameters.Count);
}
// Inherited Functions
public override void Delete()
{
DatabaseQuery query = new DatabaseQuery(Database.Tables.LEAVE_DATES);
DoDelete(query);
StaffsManager.Remove(this);
}
// Inherited Functions
public override void Delete()
{
DatabaseQuery query = new DatabaseQuery(Database.Tables.POSTAL_CODES);
DoDelete(query);
AddressManager.Remove(this);
}
public override void Load()
{
DatabaseQuery query = new DatabaseQuery(Database.Tables.PRESCRIPTIONS);
query.Add(Database.Tables.Prescriptions.PrescriberId);
query.Add(Database.Tables.Prescriptions.PatientId);
query.Add(Database.Tables.Prescriptions.Name);
query.Add(Database.Tables.Prescriptions.Remark);
query.Add(Database.Tables.Prescriptions.StartDate);
query.Add(Database.Tables.Prescriptions.EndDate);
MySqlDataReader reader = DoLoad(query);
if (Loaded)
{
Prescriber = StaffsManager.GetMedicalStaff(reader.GetInt32(0));
Patient = PatientsManager.GetPatient(reader.GetInt32(1));
Name = reader.GetString(2);
Remark = reader.GetString(3);
StartDate = reader.GetDateTime(4);
EndDate = reader.GetDateTime(5);
PatientsManager.Add(this);
}
reader.Close();
// TODO: Implement fetching prescription's medications.
}
public static void HandleMigrateCashShop(MapleClient client, InPacket iPacket)
{
int accountID;
int characterID = iPacket.ReadInt();
if ((accountID = MasterServer.Instance.Shop.Migrations.Validate(client.Host, characterID)) == -1)
{
client.Close();
return;
}
using (DatabaseQuery query = Database.Query("SELECT * FROM `accounts` WHERE `account_id` = @account_id", new MySqlParameter("account_id", accountID)))
{
query.NextRow();
client.Account = new Account(query);
}
using (DatabaseQuery query = Database.Query("SELECT * FROM `characters` WHERE `character_id` = @character_id", new MySqlParameter("character_id", characterID)))
{
query.NextRow();
client.Character = new Character(client, query);
}
client.Character.Initialize(true);
}
/// <summary>
/// Unregisters gamer from bracket
/// </summary>
///
/// <param name="systemID">
/// System ID associated with user
/// </param>
///
/// <param name="bracketID">
/// Bracket ID associated with bracket
/// </param>
///
/// <returns>
/// Boolean indicated success or fail in unregistration
/// </returns>
public bool UnregisterGamerFromBracket(int systemID, int bracketID)
{
/*
* Status codes
*
* 2 - bracket is in progress
* 1 - bracket not in progress and has already completed
* 0 - bracket not in progress and has not begun
*/
try
{
DatabaseQuery databaseQuery = new DatabaseQuery();
TournamentBracketDatabaseQuery tournamentBracketDatabaseQuery = new TournamentBracketDatabaseQuery();
string hashedUserID = databaseQuery.GetHashedUserID(systemID);
BracketInfo bracket = tournamentBracketDatabaseQuery.GetBracketInfo(bracketID);
if (bracket.StatusCode == 2)
{
return(tournamentBracketDatabaseQuery.DisqualifyGamerFromBracket(bracketID, hashedUserID));
}
else if (bracket.StatusCode == 0)
{
bool removeResult = tournamentBracketDatabaseQuery.RemoveGamerFromBracket(hashedUserID, bracketID);
bool updateResult = tournamentBracketDatabaseQuery.UpdateBracketPlayerCount(bracketID, 0);
if (removeResult && updateResult)
{
return(true);
}
}
return(false);
}
catch
{
return(false);
}
}
public override void Load()
{
DatabaseQuery query = new DatabaseQuery(Database.Tables.TEST_RESULTS);
query.Add(Database.Tables.TestResults.PatientId);
query.Add(Database.Tables.TestResults.MedicalLicenseNo);
query.Add(Database.Tables.TestResults.Name);
query.Add(Database.Tables.TestResults.Description);
query.Add(Database.Tables.TestResults.Result);
query.Add(Database.Tables.TestResults.Remark);
MySqlDataReader reader = DoLoad(query);
if (Loaded)
{
Patient = PatientsManager.GetPatient(reader.GetInt32(0));
MedicalLicenseNo = reader.GetString(1);
Name = reader.GetString(2);
Description = reader.GetString(3);
Result = reader.GetString(4);
Remark = reader.GetString(5);
PatientsManager.Add(this);
}
reader.Close();
}
public async Task <IActionResult> UpdateCar(int id, [FromBody] Car body)
{
Console.WriteLine("Controller: Update car with id:" + id);
await Db.Connection.OpenAsync();
var query = new DatabaseQuery(Db);
var result = await query.FindOneCarAsync(id);
if (result is null)
{
return(new NotFoundResult());
}
//check if new licenace is in database - if so, return.
var result2 = await query.FindOneCarAsync(body.LicensePlate);
if ((result != null) && result2.Id != result.Id)
{
return(Content("{\"status\":\"Error License plate in DB!\"}"));
}
//insert type id to list if needed and return the id of updated type
int type_id = await query.GetTypeID(body.CarType);
body.carTypeId = type_id;
body.Db = Db;
//copy and update.
result.Copy(body);
await result.UpdateAsync();
return(new OkObjectResult("{\"status\":\"OK\"}"));
}
public static DatabaseQuery GetInEqualQuery(string tableName, string columnName, object value)
{
DatabaseQuery subquery = new DatabaseQuery(tableName);
subquery.Comparator = ManagerHelper.GetEqualComparator(columnName, value);
subquery.Add(Database.Tables.Generic.ID);
return(subquery);
}
public override void Save()
{
DatabaseQuery query = new DatabaseQuery(Database.Tables.COUNTRIES);
query.Add(Database.Tables.Countries.Name, Name);
DoSave(query);
AddressManager.Add(this);
}
//********************************************************************
// DELETE QUERIES
//********************************************************************
/// <summary>
/// Permanently deletes an answer from the database.
/// </summary>
/// <param name="answer"></param>
public static void DeleteAnswer(Answer answer)
{
if (answer.AnsID <= 0) {
throw new Exception("Invalid answer to delete, the AnsID = " + answer.AnsID);
}
string queryString = "delete from Answers where Ans_ID = " + answer.AnsID;
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Delete);
}
public static QueryComparator GetInComparator(string columnName, DatabaseQuery subquery)
{
QueryComparator comparator = new QueryComparator();
comparator.Source = new QueryElement(columnName);
comparator.Operand = new QueryElement(subquery);
return(comparator);
}
/// <summary>
/// Permanently deletes a qualifier from the database and from the study it was being used in.
/// </summary>
/// <param name="qualifier"></param>
/// <param name="study"></param>
public static void DeleteQualifier(Qualifier qualifier, Study study)
{
if (qualifier.QualID <= 0) {
throw new Exception("Invalid qualifier to delete, the qualID = " + qualifier.QualID);
}
if (study.StudyID <= 0) {
throw new Exception("Invalid study to delete, the studyID = " + study.StudyID);
}
string queryString = "delete from Study_Qualifiers where Qual_ID = " + qualifier.QualID + " and Study_ID = " + study.StudyID;
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Delete);
}
/// <summary>
/// Constructor that runs a query to populate all fields of a Researcher object
/// </summary>
/// <param name="user_id">The id of the researcher to create an object for</param>
public Researcher(int user_id)
{
string queryString = "select User_Name, First_Name, Last_Name, Email from Researcher where Res_ID = " + user_id.ToString();
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Select);
this.userID = user_id;
this.userName = query.Results[0][0];
this.firstName = query.Results[0][1];
this.lastName = query.Results[0][2];
this.email = query.Results[0][3];
}
/// <summary>
/// Constructor used to query the database and create an answer object based on the id
/// passed in. The ID must exist in the database or this will fail.
/// </summary>
/// <param name="ansID"></param>
public Answer(int ansID)
{
string queryString = "select Answer, Rank from Answers where Ans_ID = " + ansID;
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Select);
this.answerText = query.Results[0][0];
this.score = Convert.ToInt32(query.Results[0][1]);
this.ansID = ansID;
queryString = "select Qual_ID from Answers where Ans_ID = " + ansID;
query = new DatabaseQuery(queryString, DatabaseQuery.Type.Select);
int qualID = Convert.ToInt32(query.Results[0][0]);
this.qualifier = new Qualifier(qualID);
}
/// <summary>
/// Constructor used to manually build a qualifier, however it will still try to query the database to get the answers.
/// In general, the other two constructors are more practical to use - this should only be used in specific cases.
/// </summary>
/// <param name="qualID"></param>
/// <param name="question"></param>
/// <param name="description"></param>
/// <param name="resID"></param>
public Qualifier(int qualID, string question, string description, int resID)
{
this.qualID = qualID;
this.question = question;
this.description = description;
this.resID = resID;
string queryString = "select Ans_ID, Answer, Rank from Answers where Qual_ID = " + qualID;
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Select);
foreach (List<String> result in query.Results) {
int ansID = Convert.ToInt32(result[0]);
string answer = result[1];
int score = Convert.ToInt32(result[2]);
Answer tempAnswer = new Answer(ansID, answer, score, this);
this.answers.Add(tempAnswer);
}
}
private bool AuthenticateUser(string p_UserName, string p_Password, RequestContext p_RequestContext)
{
try
{
var l_PassHash = "";
using (var l_SelectQuery = new DatabaseQuery(Database.Connection, true))
{
l_SelectQuery.AppendLine("SELECT PassHash");
l_SelectQuery.AppendLine(" FROM Periscopix.dbo.prscpxUsers");
l_SelectQuery.AppendLine(" WHERE Company = 1 AND UserName = @Username");
l_SelectQuery.AddWithValue("@Username", p_UserName);
using (var l_Reader = new ExDataReader(l_SelectQuery.ExecuteReader()))
{
if (l_Reader.Read())
{
l_PassHash = l_Reader.GetString();
}
}
}
var l_Success = BCrypt.CheckPassword(p_Password, l_PassHash);
if (l_Success)
{
CreateSuccessReply(ref p_RequestContext, l_PassHash);
}
return true;
}
catch
{
return false;
}
}
/// <summary>
/// Populate the listbox with all the studies, order by Name
/// </summary>
/// <param name="user_id"></param>
private void populateListbox(int user_id)
{
// local variables
Study study = null;
ListItem item = null;
string queryString = "Select Study_ID, Name from Study where Res_ID = " + user_id + " order by Name";
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Select);
int resultNum = 0;
// Clear any unwanted data out of the list box
lboxStudyList.Items.Clear();
// loop to populate researchers own studies
while (query.Results.Count > resultNum) {
study = new Study(Convert.ToInt32(query.Results[resultNum][0]));
item = new ListItem(study.Name, study.StudyID.ToString());
lboxStudyList.Items.Add(item);
resultNum++;
}
}
/// <summary>
/// Event handler for when a Participant tries to login.
/// On Success they will be directed to the Participant Form.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnParSubmit_Click(object sender, EventArgs e)
{
string queryString = "select Par_ID, user_name, first_name, last_name, email from Participant where user_name = '" + tbParUser.Text + "' and password = '******'";
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Select);
if (query.Results.Count == 0) {
lblParStatus.Text = "Invalid login. Please try again.";
}
else if (query.Results.Count == 1) {
int user_id = Convert.ToInt32(query.Results[0][0]);
string user_name = query.Results[0][1];
string first_name = query.Results[0][2];
string last_name = query.Results[0][3];
string email = query.Results[0][4];
Participant par = new Participant(user_id, user_name, first_name, last_name, email, new List<Answer>());
Session["user"] = par;
Response.Redirect("ParticipantForm.aspx");
}
else {
throw new Exception("submitting user_name: " + tbParUser.Text + " returned incorrect number of rows: " + query.Results.Count);
}
}
/// <summary>
/// Constructor that runs a query to populate all fields of a Participant object
/// </summary>
/// <param name="user_id">The id of the researcher to create an object for</param>
public Participant(int user_id)
{
string queryString = "select User_Name, First_Name, Last_Name, Email from Participant where Par_ID = " + user_id.ToString();
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Select);
this.userID = user_id;
this.userName = query.Results[0][0];
this.firstName = query.Results[0][1];
this.lastName = query.Results[0][2];
this.email = query.Results[0][3];
queryString = "select Ans_ID from Participant_Answers where Par_ID = " + userID;
query = new DatabaseQuery(queryString, DatabaseQuery.Type.Select);
int count = query.Results.Count;
this.answers = new List<Answer>();
for (int i = 0; i < count; i++) {
int ansID = Convert.ToInt32(query.Results[i][0]);
Answer answer = new Answer(ansID);
this.answers.Add(answer);
}
}
/// <summary>
/// Constructor that queries the database to get all relevant information about a study
/// and create an object out of it. this constructor uses significantly more overhead than
/// the following one so it should only be used if it is not feasible to use the other one.
/// </summary>
/// <param name="studyID"></param>
public Study(int studyID)
{
string queryString = "select name, Description, Incentive, Creation_Date, Expired, Res_ID from Study where Study_ID = " + studyID.ToString();
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Select);
this.name = query.Results[0][0];
this.description = query.Results[0][1];
this.incentive = query.Results[0][2];
this.dateCreated = Convert.ToDateTime(query.Results[0][3]);
this.expired = Convert.ToBoolean(Convert.ToInt32(query.Results[0][4]));
this.researcherID = Convert.ToInt32(query.Results[0][5]);
this.studyID = studyID;
queryString = "select Q.Qual_ID, Question, Description, Res_ID from Study_Qualifiers SQ, Qualifiers Q where Study_ID = " + studyID + " and Q.Qual_ID = SQ.Qual_ID";
query = new DatabaseQuery(queryString, DatabaseQuery.Type.Select);
foreach (List<String> result in query.Results) {
int qualID = Convert.ToInt32(result[0]);
string question = result[1];
string description = result[2];
int resID = Convert.ToInt32(result[3]);
Qualifier tempQualifiers = new Qualifier(qualID, question, description, resID);
this.qualifiers.Add(tempQualifiers);
}
}
/// <summary>
/// Gets all the studies in the database and returns them in a list
/// </summary>
/// <returns></returns>
public static List<Study> GetStudies()
{
List<Study> studies = new List<Study>();
string queryString = "select study_id, name, Description, Incentive, Creation_Date, Expired, Res_ID from Study";
DatabaseQuery query1 = new DatabaseQuery(queryString, DatabaseQuery.Type.Select);
for (int i = 0; i < query1.Results.Count; i++) {
int studyID = Convert.ToInt32(query1.Results[i][0]);
string name = query1.Results[i][1];
string description = query1.Results[i][2];
string incentive = query1.Results[i][3];
DateTime dateCreated = Convert.ToDateTime(query1.Results[i][4]);
bool expired = Convert.ToBoolean(Convert.ToInt32(query1.Results[i][5]));
int researcherID = Convert.ToInt32(query1.Results[i][6]);
List<Qualifier> qualifiers = new List<Qualifier>();
queryString = "select Q.Qual_ID, Question, Description, Res_ID from Study_Qualifiers SQ, Qualifiers Q where Study_ID = " + studyID + " and Q.Qual_ID = SQ.Qual_ID";
DatabaseQuery query2 = new DatabaseQuery(queryString, DatabaseQuery.Type.Select);
foreach (List<String> result in query2.Results) {
int qualID = Convert.ToInt32(result[0]);
string question = result[1];
string qualDescription = result[2];
int resID = Convert.ToInt32(result[3]);
Qualifier tempQualifiers = new Qualifier(qualID, question, description, resID);
qualifiers.Add(tempQualifiers);
}
Study temp = new Study(studyID, name, description, incentive, dateCreated, expired, researcherID, qualifiers);
studies.Add(temp);
}
return studies;
}
//********************************************************************
// IINSERT QUERIES
//********************************************************************
/// <summary>
/// Insert an answer into the database and link it to its associated qualifier.
/// </summary>
/// <param name="answer"></param>
/// <param name="qualID"></param>
/// <returns></returns>
public static int InsertAnswer(Answer answer, int qualID)
{
List<string> scrubbedInput = sanitizeInputs(answer.AnswerText, answer.Score.ToString());
string queryString = "insert into Answers " +
"(Answer, Rank, Qual_ID) " +
"values " +
"('" + scrubbedInput[0] + "', " + Convert.ToInt32(scrubbedInput[1]) + ", " + qualID + ")";
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Insert);
return query.LastInsertID;
}
/// <summary>
/// Queries the database to compare what Qualifiers currently exist in the
/// database with the study passed in and removes them from the database if they no longer exist.
/// </summary>
/// <param name="study">Accepts the study that we are deleting the qualifier from</param>
private void removeDeletedQualifiers(Study study)
{
string queryString = "select Qual_ID from Study_Qualifiers where Study_ID = " + study.StudyID;
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Select);
List<Qualifier> oldQualifiers = new List<Qualifier>();
for (int i = 0; i < query.Results.Count; i++) {
oldQualifiers.Add(new Qualifier(Convert.ToInt32(query.Results[i][0])));
}
foreach (Qualifier oldQualifier in oldQualifiers) {
bool found = false;
foreach (Qualifier qual in study.Qualifiers) {
if (oldQualifier.QualID == qual.QualID) {
found = true;
break;
}
}
if (!found) {
DAL.DeleteQualifier(oldQualifier, study);
}
}
}
/// <summary>
/// Queries the database to compare what Answers currently exist in the
/// database with the study passed in and removes them from the database if they no longer exist.
/// </summary>
/// <param name="study">Accepts the study that we are deleting the qualifier from</param>
private void removeDeletedAnwers(Study study)
{
string queryString = "select Ans_ID from Study_Qualifiers sq, Answers a where sq.Qual_ID = a.Qual_ID and sq.Study_ID = " + study.StudyID;
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Select);
List<Answer> oldAnswers = new List<Answer>();
for (int i = 0; i < query.Results.Count; i++) {
oldAnswers.Add(new Answer(Convert.ToInt32(query.Results[i][0])));
}
foreach (Answer oldAnswer in oldAnswers) {
bool found = false;
foreach (Qualifier qual in study.Qualifiers) {
foreach (Answer ans in qual.Answers) {
if (oldAnswer.AnsID == ans.AnsID) {
found = true;
break;
}
}
}
if (!found) {
DAL.DeleteAnswer(oldAnswer);
}
}
}
/// <summary>
/// Inserts a pre-existing qualifier into the Study_Qualifier table.
/// Doesn't insert into the Qualifier table because its pre-existing.
/// </summary>
/// <param name="qualifier"></param>
/// <param name="studyID"></param>
/// <returns></returns>
public static int InsertExisingQualifier(Qualifier qualifier, int studyID)
{
string queryString = "insert into Study_Qualifiers " +
"(Qual_ID, Study_ID) " +
"values " +
"(" + qualifier.QualID + ", " + studyID + ")";
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Insert);
return query.LastInsertID;
}
/// <summary>
/// Query to return the most recently created study to use in the main panel on the front page
/// </summary>
/// <returns></returns>
public static Study GetLatestStudy()
{
string queryString = "select Study_ID, Name, Description, Incentive, Creation_Date, Expired, Res_ID from Study order by Creation_Date desc limit 1";
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Select);
List<string> result = query.Results[0];
Study study = new Study(Convert.ToInt32(result[0]), result[1], result[2], result[3], Convert.ToDateTime(result[4]), Convert.ToBoolean(Convert.ToInt32(result[5])), Convert.ToInt32(result[6]), null);
return study;
}
/// <summary>
/// Inserts a new qualifier into the database
/// </summary>
/// <param name="qualifier"></param>
/// <returns>Returns the qualifierID</returns>
public static int InsertQualifier(Qualifier qualifier, int studyID)
{
List<string> scrubbedInput = sanitizeInputs(qualifier.Question, qualifier.Description);
string queryString = "insert into Qualifiers " +
"(Question, Description, Res_ID) " +
"values " +
"('" + scrubbedInput[0] + "','" + scrubbedInput[1] + "', " + qualifier.ResID + ")";
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Insert);
int qualID = query.LastInsertID;
queryString = "insert into Study_Qualifiers " +
"(Qual_ID, Study_ID) " +
"values " +
"(" + qualID + ", " + studyID + ")";
query = new DatabaseQuery(queryString, DatabaseQuery.Type.Insert);
return qualID;
}
/// <summary>
/// Checks to see if a qualifier exists in a particular study.
/// </summary>
/// <param name="studyID">The study being checked.</param>
/// <param name="qualID">The qualifier being checked.</param>
/// <returns>True if qualifier belongs to the study. False if it belongs to another study.</returns>
public static bool QualifierExistsInStudy(int studyID, int qualID)
{
string queryString = "select 'True' " +
"from Study_Qualifiers " +
"where Study_ID = " + studyID + " and Qual_ID = " + qualID;
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Select);
try {
return Convert.ToBoolean(query.Results[0][0]);
}
catch {
return false;
}
}
//********************************************************************
// UPDATE QUERIES
//********************************************************************
/// <summary>
/// Update an answer. Note: AnsID must be set and qualID must be set for this to update properly.
/// </summary>
/// <param name="answer"></param>
public static void UpdateAnswer(Answer answer)
{
List<string> scrubbedInput = sanitizeInputs(answer.AnswerText, answer.Score.ToString());
if (answer.AnsID <= 0) {
throw new Exception("Invalid answer to update, the AnsID = " + answer.AnsID);
}
string queryString = "update Answers set " +
"Answer = '" + scrubbedInput[0] + "', " +
"Rank = '" + Convert.ToInt32(scrubbedInput[1]) + "' " +
"where Ans_ID = " + answer.AnsID;
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Update);
}
/// <summary>
/// Update a participant in the database. Participant IDs must match.
/// </summary>
/// <param name="userName"></param>
/// <param name="firstName"></param>
/// <param name="lastName"></param>
/// <param name="email"></param>
/// <param name="password"></param>
/// <param name="parID"></param>
public static void UpdateParticipant(string userName, string firstName, string lastName, string email, string password, int parID)
{
List<string> scrubbedInput = sanitizeInputs(userName, firstName, lastName, email, password);
string queryString = "update Participant " +
"set User_Name = '" + scrubbedInput[0] + "'," +
"First_Name = '" + scrubbedInput[1] + "'," +
"Last_Name = '" + scrubbedInput[2] + "'," +
"Email = '" + scrubbedInput[3] + "'," +
"Password = '******' " +
"where Par_ID = " + parID;
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Update);
}
/// <summary>
/// Updates a qualifier. NOTE: the qualifier must have its qualID field properly set or it will throw an exception.
/// </summary>
/// <param name="qualifier"></param>
public static void UpdateQualifier(Qualifier qualifier)
{
List<string> scrubbedInput = sanitizeInputs(qualifier.Question, qualifier.Description);
if (qualifier.QualID <= 0) {
throw new Exception("Invalid qualifier to update, the QualID= " + qualifier.QualID);
}
string queryString = "update Qualifiers set " +
"Question = '" + scrubbedInput[0] + "'," +
"Description = '" + scrubbedInput[1] + "'," +
"Res_ID = " + qualifier.ResID + " " +
"where Qual_ID = " + qualifier.QualID;
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Update);
}
/// <summary>
/// Updates a Researcher in the database. Researcher IDs must match.
/// </summary>
/// <param name="resID"></param>
/// <param name="firstName"></param>
/// <param name="lastName"></param>
/// <param name="userName"></param>
/// <param name="email"></param>
/// <param name="password"></param>
public static void UpdateResearcher(int resID, string firstName, string lastName, string userName, string email, string password)
{
List<string> scrubbedInput = sanitizeInputs(firstName, lastName, userName, email, password);
string queryString = "update Researcher " +
"set User_Name = '" + scrubbedInput[2] + "'," +
"First_Name = '" + scrubbedInput[0] + "'," +
"Last_Name = '" + scrubbedInput[1] + "'," +
"Email = '" + scrubbedInput[3] + "'," +
"Password = '******' " +
"where Res_ID = " + resID;
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Update);
}
/// <summary>
/// Updates a specific study. NOTE: the study must have its studyID field properly set or it will throw an exception.
/// </summary>
/// <param name="study"></param>
public static void UpdateStudy(Study study)
{
List<string> scrubbedInput = sanitizeInputs(study.Name, study.Description, study.Incentive);
if (study.StudyID <= 0) {
throw new Exception("Invalid study to update, the studyID = " + study.StudyID);
}
string queryString = "update Study set " +
"Name = '" + scrubbedInput[0] + "', " +
"Description = '" + scrubbedInput[1] + "', " +
"Incentive = '" + scrubbedInput[2] + "', " +
"Expired = " + Convert.ToInt32(study.Expired) + " " +
"where Study_ID = " + study.StudyID;
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Update);
}
/// <summary>
///
/// </summary>
/// <param name="ResID"></param>
/// <returns></returns>
public static List<string> GetResearcher(int ResID)
{
List<string> resNameEmail = new List<string>();
string queryString = "select User_Name, Email " +
"from Researcher " +
"where Res_ID = " + ResID;
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Select);
resNameEmail.Add(query.Results[0][0]);
resNameEmail.Add(query.Results[0][1]);
return resNameEmail;
}
/// <summary>
/// Insert that a specific participant has selected a specific answer to a qualifier.
/// </summary>
/// <param name="partID"></param>
/// <param name="answerID"></param>
/// <returns></returns>
public static int InsertParticipantAnswer(int partID, int answerID)
{
string queryString = "insert into Participant_Answers " +
"(Par_ID, Ans_ID) " +
"values " +
"(" + partID + ", " + answerID + ")";
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Insert);
return query.LastInsertID;
}
/// <summary>
/// Gets all of the answers that a participant has answered so far
/// </summary>
/// <param name="partID"></param>
/// <returns></returns>
public static List<int> GetParticipantAnswers(int partID)
{
List<int> answerIDs = new List<int>();
string queryString = "select Ans_ID " +
"from Participant_Answers " +
"where Par_ID = " + partID;
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Select);
for (int i = 0; i < query.Results.Count; i++) {
answerIDs.Add(Convert.ToInt32(query.Results[i][0]));
}
return answerIDs;
}
/// <summary>
/// Inserts a researcher into the database.
/// </summary>
/// <param name="userName"></param>
/// <param name="firstName"></param>
/// <param name="lastName"></param>
/// <param name="email"></param>
/// <param name="password"></param>
public static void InsertResearcher(string userName, string firstName, string lastName, string email, string password)
{
List<string> scrubbedInput = sanitizeInputs(userName, firstName, lastName, email, password);
string queryString = "insert into Researcher" +
" (User_Name, First_Name, Last_Name, Email, Password, Num_Ratings)" +
" values " +
" ('" + scrubbedInput[0] + "', '" + scrubbedInput[1] + "','" + scrubbedInput[2] + "', '" + scrubbedInput[3] + "', '" + scrubbedInput[4] + "',0)";
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Insert);
}
/// <summary>
/// Inserts a new study into the database
/// </summary>
/// <param name="study"></param>
/// <returns>Returns the studyID of the newly created study</returns>
public static int InsertStudy(Study study)
{
List<string> scrubbedInput = sanitizeInputs(study.Name, study.Description, study.Incentive, study.ResearcherID.ToString());
string queryString = "insert into Study " +
"(Name, Description, Incentive, Creation_Date, Expired, Res_ID) " +
"values " +
"('" + scrubbedInput[0] + "','" + scrubbedInput[1] + "','" + scrubbedInput[2] + "', NOW(), 0, " + scrubbedInput[3] + ")";
DatabaseQuery query = new DatabaseQuery(queryString, DatabaseQuery.Type.Insert);
return query.LastInsertID;
}
