function check_competency_result_access($userId, $crId, &$found)
{
// select the first assessment_worksheet which (through several layers of
// indirection) is referenced by the competency item
$query = new Query(new QueryBuilder(SELECT_QUERY, array('tables' => array('assessment_worksheet' => 'id'), 'joins' => array("INNER JOIN rubric_results ON rubric_results.id = assessment_worksheet.fk_rubric_results", "INNER JOIN competency_results ON competency_results.fk_rubric_results = rubric_results.id"), 'where' => "competency_results.id = ?", 'where-params' => array("i:{$crId}"))));
if ($query->is_empty()) {
$found = false;
return false;
}
$found = true;
// then verify that we have access to the worksheet for some assessment
$wkstId = $query->get_row_ordered()[0];
return check_assessment_access($userId, $wkstId, 'assessment_worksheet');
}
function get_wkst($id)
{
// verify access to worksheet
if (!abet_is_admin_authenticated() && !abet_is_observer() && !check_assessment_access($_SESSION['id'], $id, 'assessment_worksheet')) {
page_fail(UNAUTHORIZED);
}
// select required data from db
$query = new Query(new QueryBuilder(SELECT_QUERY, array('tables' => array('assessment_worksheet' => array('id', 'activity', 'objective', 'instrument', 'course_of_action'), 'abet_assessment' => 'id', 'course' => array('title', 'course_number'), 'abet_characteristic' => array('level', 'program_specifier', 'description'), 'abet_criterion' => array('rank', 'description')), 'joins' => array('INNER JOIN abet_assessment ON assessment_worksheet.fk_assessment = abet_assessment.id', 'LEFT OUTER JOIN course ON assessment_worksheet.fk_course = course.id', 'LEFT OUTER JOIN abet_characteristic ON abet_assessment.fk_characteristic = abet_characteristic.id', 'INNER JOIN abet_criterion ON abet_assessment.fk_criterion = abet_criterion.id'), 'where' => 'assessment_worksheet.id = ?', 'where-params' => array("i:{$id}"))));
if ($query->is_empty()) {
page_fail(NOT_FOUND);
}
$row = $query->get_row_assoc();
// query the assessment personnel via the acl
$assess = new ABETAssessment($row['abet_assessment.id']);
$faculty = $assess->get_acl();
// build object for client
$obj = new stdClass();
$obj->id = $row['assessment_worksheet.id'];
$obj->faculty = count($faculty) == 0 ? "n/a" : implode(', ', array_map(function ($x) {
return $x->full_name;
}, $faculty));
$obj->criterion = "{$row['rank']} {$row['abet_criterion.description']}";
if (!is_null($row['level'])) {
$obj->characteristic = "{$row['level']} {$row['abet_characteristic.description']}";
if (!is_null($row['program_specifier']) && $row['program_specifier'] != '') {
$obj->characteristic .= " {$row['program_specifier']}";
}
} else {
$obj->characteristic = null;
}
if (!is_null($row['title'])) {
$obj->course = "{$row['course_number']}: {$row['title']}";
$obj->activity = null;
} else {
$obj->course = null;
$obj->activity = !is_null($row['activity']) && $row['activity'] != '' ? $row['activity'] : 'not specified';
}
$obj->objective = $row['objective'];
$obj->instrument = $row['instrument'];
$obj->course_of_action = $row['course_of_action'];
return json_encode($obj);
}
function create_course($title, $courseNumber, $coordinator, $instructor, $description, $textbook, $creditHours)
{
if (is_null($title) || $title == "") {
page_fail_on_field(BAD_REQUEST, 'title', 'must be non-empty');
}
if (is_null($courseNumber) || $courseNumber == "") {
page_fail_on_field(BAD_REQUEST, 'course_number', 'must be non-empty');
}
if (is_null($coordinator)) {
page_fail_on_field(BAD_REQUEST, 'coordinator', 'must be non-empty');
}
if (is_null($instructor) || $instructor == "") {
page_fail_on_field(BAD_REQUEST, 'instructor', 'must be non-empty');
}
if (is_null($description) || $description == "") {
page_fail_on_field(BAD_REQUEST, 'description', 'must be non-empty');
}
if (is_null($textbook) || $textbook == "") {
page_fail_on_field(BAD_REQUEST, 'textbook', 'must be non-empty');
}
if (is_null($creditHours) || $creditHours == "") {
page_fail_on_field(BAD_REQUEST, 'credit_hours', 'must be non-empty');
}
$info = array('table' => 'course', 'fields' => array('title', 'course_number', 'fk_coordinator', 'instructor', 'description', 'textbook', 'credit_hours'), 'values' => array(array("s:{$title}", "s:{$courseNumber}", "i:{$coordinator}", "s:{$instructor}", "s:{$description}", "s:{$textbook}", "s:{$creditHours}")));
list($code, $json) = Query::perform_transaction(function (&$rollback) use($info) {
$insert = new Query(new QueryBuilder(INSERT_QUERY, $info));
if (!$insert->validate_update()) {
$rollback = true;
return array(SERVER_ERROR, "{\"success\":false}");
}
$query = new Query(new QueryBuilder(SELECT_QUERY, array('tables' => array('course' => array('id', 'title', 'fk_coordinator', 'instructor', 'description', 'textbook', 'credit_hours')), 'aliases' => array('course.fk_coordinator' => 'coordinator'), 'where' => 'course.id = LAST_INSERT_ID()')));
if ($query->is_empty()) {
$rollback = true;
return array(SERVER_ERROR, "{\"success\":false}");
}
return array(OKAY, json_encode($query->get_row_assoc()));
});
http_response_code($code);
return $json;
}
if (!ctype_alpha($_POST['username'][0])) {
echo json_encode(array("error" => "username must begin with alphabetic character", "errField" => "username"));
http_response_code(BAD_REQUEST);
exit;
}
// validate user role; must be one of 'admin', 'faculty', 'observer'
if ($_POST['role'] != 'faculty' && $_POST['role'] != 'admin' && $_POST['role'] != 'observer') {
page_fail_on_field(BAD_REQUEST, 'role', 'role must be one of \'faculty\', \'admin\' or \'observer\'');
}
// perform a transaction that will atomically check the database and do an
// insert
list($code, $json) = Query::perform_transaction(function (&$rollback) {
// make sure username is not already in use for another user
$query = new Query(new QueryBuilder(SELECT_QUERY, array('tables' => array('userprofile' => 'username'), 'where' => 'username = ? AND id <> ?', 'where-params' => array("s:{$_POST['username']}", "s:{$_SESSION['id']}"), 'limit' => 1)));
// check select result
if (!$query->is_empty()) {
$rollback = true;
return array(BAD_REQUEST, json_encode(array("error" => "the requested username is unavailable", "errField" => "username")));
}
// insert new 'userauth' entity
$hash = password_hash($_POST['passwd'], PASSWORD_DEFAULT);
$query = new Query(new QueryBuilder(INSERT_QUERY, array('table' => 'userauth', 'fields' => array('passwd', 'role'), 'values' => array(array("s:{$hash}", "s:{$_POST['role']}")))));
if (!$query->validate_update()) {
$rollback = true;
return array(SERVER_ERROR, "{\"success\":false}");
}
// insert new 'userprofile' entity with foreign key to the newly created
// 'userauth' entity; we use the password hash to identify the userauth instance
$query = new Query(new QueryBuilder(INSERT_QUERY, array('table' => 'userprofile', 'fields' => array('fk_userauth', 'username', 'created'), 'select' => array('tables' => array('userauth' => 'id', 1 => array("'{$_POST['username']}'", "NOW()")), 'where' => "passwd = '{$hash}'"))));
if (!$query->validate_update()) {
$rollback = true;
function get_assessment($id)
{
// get general assessment information
$query = new Query(new QueryBuilder(SELECT_QUERY, array('tables' => array('abet_assessment' => 'name', 'abet_characteristic' => 'id', 'general_content' => 'id'), 'joins' => array('LEFT OUTER JOIN abet_characteristic ON abet_characteristic.id = abet_assessment.fk_characteristic', 'LEFT OUTER JOIN general_content ON general_content.fk_assessment = abet_assessment.id'), 'where' => 'abet_assessment.id = ?', 'where-params' => array("i:{$id}"), 'limit' => 1)));
if ($query->is_empty()) {
page_fail(NOT_FOUND);
}
// get acl and profile information
$aclQuery = new Query(new QueryBuilder(SELECT_QUERY, array('tables' => array('userprofile' => array('id', 'first_name', 'last_name'), 'abet_assessment' => 'id'), 'joins' => array('LEFT OUTER JOIN acl_entry ON acl_entry.fk_profile = userprofile.id', 'LEFT OUTER JOIN acl ON acl.id = acl_entry.fk_acl', 'LEFT OUTER JOIN abet_assessment ON abet_assessment.fk_acl = acl.id ' . 'AND abet_assessment.id = ' . intval($id)), 'orderby' => 'userprofile.last_name')));
if ($aclQuery->is_empty()) {
// this shouldn't happen
page_fail(NOT_FOUND);
}
// get worksheet information
$contentQuery = new Query(new QueryBuilder(SELECT_QUERY, array('tables' => array('assessment_worksheet' => array('id', 'activity'), 'course' => 'course_number'), 'joins' => array('INNER JOIN abet_assessment ON abet_assessment.id = assessment_worksheet.fk_assessment', 'LEFT OUTER JOIN course ON course.id = assessment_worksheet.fk_course'), 'where' => 'abet_assessment.id = ?', 'where-params' => array("i:{$id}"))));
// get characteristics information
$charsQuery = new Query(new QueryBuilder(SELECT_QUERY, array('tables' => array('abet_characteristic' => array('id', 'level', 'program_specifier', 'short_name')), 'orderby' => 'CHAR_LENGTH(level), level')));
// get single entity rows
$genInfo = $query->get_row_assoc();
// prepare assessment object
$obj = new stdClass();
$obj->name = $genInfo['name'];
$obj->characteristic = $genInfo['abet_characteristic.id'];
$obj->has_content = !is_null($genInfo['general_content.id']);
$obj->acl = array();
$obj->profiles = array();
$obj->worksheets = array();
$obj->characteristics = array();
// assign profile and acl information
$m = array();
$aclQuery->for_each_assoc(function ($row) use($obj, &$m) {
if (array_key_exists($row['userprofile.id'], $m)) {
return;
}
$m[$row['userprofile.id']] = null;
if (!is_null($row['abet_assessment.id'])) {
$obj->acl[] = $row['userprofile.id'];
}
$p = new stdClass();
$p->id = $row['userprofile.id'];
$p->name = "{$row['first_name']} {$row['last_name']}";
$obj->profiles[] = $p;
});
// assign worksheet info
$contentQuery->for_each_assoc(function ($row) use($obj) {
$w = new stdClass();
$w->id = $row['id'];
if (!is_null($row['activity'])) {
$w->name = $row['activity'];
} else {
$w->name = $row['course_number'];
}
$obj->worksheets[] = $w;
});
// assign characteristics
$charsQuery->for_each_assoc(function ($row) use($obj) {
$c = new stdClass();
$c->id = $row['id'];
$c->name = "{$row['level']}. {$row['short_name']}";
if (!is_null($row['program_specifier'])) {
$c->name .= " [{$row['program_specifier']}]";
}
$obj->characteristics[] = $c;
});
return json_encode($obj);
}
function has_general_content()
{
$query = new Query(new QueryBuilder(SELECT_QUERY, array('tables' => array('general_content' => 'id'), 'where' => "general_content.fk_assessment = {$this->id}")));
return !$query->is_empty();
}
function add_comp_row($id)
{
// 'id' is worksheet id
return Query::perform_transaction(function (&$rollback) use($id) {
// select id of rubric_results entity
$query = new Query(new QueryBuilder(SELECT_QUERY, array('tables' => array('rubric_results' => 'id'), 'joins' => array('INNER JOIN assessment_worksheet ON assessment_worksheet.fk_rubric_results = rubric_results.id'), 'where' => 'assessment_worksheet.id = ?', 'where-params' => array("i:{$id}"))));
if ($query->is_empty()) {
page_fail(NOT_FOUND);
}
$rrId = $query->get_row_ordered()[0];
// insert new competency_results entity
$insert = new Query(new QueryBuilder(INSERT_QUERY, array('table' => 'competency_results', 'fields' => array('outstanding_tally', 'expected_tally', 'marginal_tally', 'unacceptable_tally', 'fk_rubric_results'), 'values' => array(array("l:0", "l:0", "l:0", "l:0", "l:{$rrId}")))));
// select the inserted row and return it
$comp = new Query(new QueryBuilder(SELECT_QUERY, array('tables' => array('competency_results' => array('id', 'competency_desc', 'outstanding_tally', 'expected_tally', 'marginal_tally', 'unacceptable_tally', 'pass_fail_type', 'comment')), 'aliases' => array('competency_results.competency_desc' => 'description'), 'where' => 'id = LAST_INSERT_ID()')));
if ($comp->is_empty()) {
page_fail(SERVER_ERROR);
}
// shouldn't happen
return json_encode($comp->get_row_assoc());
});
}
function create_characteristic($level, $shortName, $description, $programSpecifier)
{
if (is_null($level) || $level == "") {
page_fail_on_field(BAD_REQUEST, 'level', 'must be non-empty');
}
if (is_null($shortName) || $shortName == "") {
page_fail_on_field(BAD_REQUEST, 'short_name', 'must be non-empty');
}
if (is_null($description) || $description == "") {
page_fail_on_field(BAD_REQUEST, 'description', 'must be non-empty');
}
$info = array('table' => 'abet_characteristic', 'fields' => array('level', 'short_name', 'description'), 'values' => array(array("s:{$level}", "s:{$shortName}", "s:{$description}")));
if (!is_null($programSpecifier) && $programSpecifier != "") {
$info['fields'][] = 'program_specifier';
$info['values'][0][] = "s:{$programSpecifier}";
}
list($code, $json) = Query::perform_transaction(function (&$rollback) use($info) {
$insert = new Query(new QueryBuilder(INSERT_QUERY, $info));
if (!$insert->validate_update()) {
$rollback = true;
return array(SERVER_ERROR, "{\"success\":false}");
}
$query = new Query(new QueryBuilder(SELECT_QUERY, array('tables' => array('abet_characteristic' => array('id', 'level', 'short_name', 'description', 'program_specifier')), 'where' => 'abet_characteristic.id = LAST_INSERT_ID()')));
if ($query->is_empty()) {
$rollback = true;
return array(SERVER_ERROR, "{\"success\":false}");
}
return array(OKAY, json_encode($query->get_row_assoc()));
});
http_response_code($code);
return $json;
}