PHP Query::get_row_assoc方法代码示例

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;
}

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);
}

     if (is_null($row)) {
         page_fail(NOT_FOUND);
     }
     echo json_encode($row);
 } else {
     // create new program
     list($code, $json) = Query::perform_transaction(function (&$rollback) {
         // insert new row for new program
         $insert = new Query(new QueryBuilder(INSERT_QUERY, array('table' => 'program', 'fields' => array('name'), 'values' => array(array("l:'New Program'")))));
         if (!$insert->validate_update()) {
             $rollback = true;
             return array(SERVER_ERROR, "an insertion operation failed");
         }
         // grab the new program object
         $query = new Query(new QueryBuilder(SELECT_QUERY, array('tables' => array('program' => array('id', 'name', 'abbrv', 'semester', 'year', 'description')), 'where' => 'id = LAST_INSERT_ID()')));
         $row = $query->get_row_assoc();
         if (is_null($row)) {
             $rollback = true;
             return array(SERVER_ERROR, "could not retrieve inserted row");
         }
         // create a default assessment for each criterion that has a general
         // content item
         foreach (range(1, 8) as $critId) {
             $assess = ABETAssessment::create('', $row['id'], null, $critId);
             $assess->add_general_content();
         }
         return array(OKAY, json_encode($row));
     });
     http_response_code($code);
     echo $json;
 }

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;
}