function getSelectedData()
{
$sql = "select prjm_id,prj_id,prjm_id,prjtg_id,milestone,year,trim(afko) as afko,\n" . "trim(description) as description,\n" . "tutor_owner,valid_until,assessment_due, \n" . "trim(coalesce(alias,'g'||grp_num)) as grp_alias, grp_num \n" . " from prj_grp pg join all_prj_tutor apt using(prjtg_id) where prjm_id=" . $this->prjm_id . ' and snummer=' . $this->judge;
$resultSet = $this->dbConn->Execute($sql);
if ($resultSet === false) {
echo "<br>Cannot get project data with <pre>\"" . $sql . '"</pre>, cause ' . $this->dbConn->ErrorMsg() . "<br>";
stacktrace(1);
die;
}
if ($resultSet->EOF) {
return $this->nullResult;
}
return $resultSet->fields;
}
function getSelectedData()
{
if ($this->dataCache != null) {
return $this->dataCache;
}
$sql = $this->query . " limit 1";
$resultSet = $this->dbConn->Execute($sql);
if ($resultSet === false) {
echo "<br>Cannot get project data with <pre>\"" . $sql . '"</pre>, cause ' . $this->dbConn->ErrorMsg() . "<br>";
stacktrace(1);
die;
}
$this->dataCache = $resultSet->fields;
return $this->dataCache;
}
function getTable($query, $triggerColumn)
{
$result = '';
$resultSet = $this->dbConn->Execute($query);
if ($resultSet === false) {
$result .= "<pre>Cannot read table data with \n\t" . $query . " \n\treason \n\t" . $this->dbConn->ErrorMsg() . "at\n";
ob_start();
stacktrace(1);
$result .= ob_get_contents();
ob_clean();
$result .= "</pre>";
return $result;
}
$tableHead = $this->tabledef . "<tr>" . $this->rowHeaderBuilder->buildHeader($resultSet->fields);
$tableBody = '';
$tableRow = '';
$oldTrigger = '';
$rowCounter = -1;
$columnCounter = 0;
while (!$resultSet->EOF) {
if ($oldTrigger != $resultSet->fields[$triggerColumn]) {
if ($tableRow != '') {
$tableBody .= $tableRow . "</tr>\n";
}
$tableRow = "<tr>" . $this->rowHeaderBuilder->build($resultSet->fields);
$oldTrigger = $resultSet->fields[$triggerColumn];
$columnCounter = 0;
$rowCounter++;
}
$tableRow .= $this->tableCellBuilder->build($resultSet->fields);
$columnCounter++;
$padding = $columnCounter < 10 ? ' ' : '';
if ($rowCounter == 0) {
$tableHead .= "\t\t<th title='" . $resultSet->fields['checktitle'] . "' class='hasnote noteblue'>" . $padding . $columnCounter . "</th>\n";
}
$resultSet->moveNext();
}
$tableBody .= $tableRow . "</tr>\n</table>\n";
return $tableHead . "</tr>" . $tableBody;
}
function getTable($query, $triggerColumn)
{
$result = '';
$resultSet = $this->dbConn->Execute($query);
if ($resultSet === false) {
$result .= "<pre>Cannot read table data with \n\t" . $query . " \n\treason \n\t" . $this->dbConn->ErrorMsg() . "at\n";
ob_start();
stacktrace(1);
$result .= ob_get_contents();
ob_clean();
$result .= "</pre>";
return $result;
}
$tableHead = $this->tabledef . "\n<tr>" . $this->rowFactory->buildHeader($resultSet->fields);
$tableBody = '';
$tableRow = '';
$oldTrigger = '';
$rowCounter = -1;
$columnCounter = 0;
while (!$resultSet->EOF) {
if ($oldTrigger != $resultSet->fields[$triggerColumn]) {
if ($tableRow != '') {
$tableBody .= $tableRow . "\n</tr>\n";
}
$tableRow = $this->rowFactory->startRow($resultSet->fields);
$oldTrigger = $resultSet->fields[$triggerColumn];
$columnCounter = 0;
$rowCounter++;
}
if ($rowCounter == 0 && $oldTrigger == $resultSet->fields[$triggerColumn]) {
$tableHead .= $this->rowFactory->buildHeaderCell($resultSet->fields);
}
$tableRow .= $this->rowFactory->buildCell($resultSet->fields);
$columnCounter++;
$padding = $columnCounter < 10 ? ' ' : '';
$resultSet->moveNext();
}
$tableBody .= $tableRow;
return $tableHead . "\n</tr>\n" . $tableBody . "\n</tr>\n</table>\n";
}
$tutor_select = "";
} else {
$tutor_select = " and (tutor='{$tutor}' or tutor_owner='{$tutor}') ";
}
ob_start();
$prjSel->setWhere("assessment_due <now() and pm.prj_milestone_open=true");
$prj_id_selector = $prjSel->getWidget();
$sqlhead = "select afko as code,pm.milestone as milstn,pt.grp_num,\n" . "s.snummer as snmailto,s.snummer,\n" . "achternaam||coalesce(', '||voorvoegsel,'') as achternaam\n" . ",roepnaam, s.snummer,pm.assessment_due as due,tutor\n";
$sqllate = "( select distinct snummer from prj_grp \n" . "natural join prj_tutor pt \n" . "join tutor t on(userid=tutor_id)\n" . "natural join prj_milestone \n" . "where written =false \n" . "and prj_milestone_open=true \n" . "and prj_grp_open=true \n" . "and assessment_due < now()::date \n" . "and prjm_id={$prjm_id})";
$sqltail = " \n" . " join milestone_open_past_due mopd on(jnr.prjtg_id=mopd.prjtg_id)" . " join prj_grp_open pgo on(pgo.prjtg_id=jnr.prjtg_id)\n" . " join student s on (jnr.snummer=s.snummer) \n" . " join prj_tutor pt on(jnr.prjtg_id=pt.prjtg_id)\n" . " join tutor t on(userid=tutor_id)\n" . " join prj_milestone pm on(pt.prjm_id=pm.prjm_id)\n" . " join project p on(p.prj_id=pm.prj_id)\n" . " where pm.prjm_id={$prjm_id}\n";
//$dbConn->log($sql);
$latecountsql = "select count(*) as latecount from {$sqllate} foo";
$resultSet = $dbConn->Execute($latecountsql);
if ($resultSet === false) {
echo "<br>Cannot get latecount with <pre>{$latecountsql}</pre>, cause" . $dbConn->ErrorMsg() . "<br>";
stacktrace(1);
die;
}
$latecount = $resultSet->fields['latecount'];
$mailbutton = $latecount > 0 ? "<input type='submit' name='domail' value='Send Mail'/>" : " ";
$sql = $sqlhead . " from \n" . "prj_grp pg \n" . "join prj_tutor pt using(prjtg_id)\n" . "join tutor t on (userid=tutor_id)\n" . "join prj_milestone pm using(prjm_id)\n" . "join project p using(prj_id)\n" . "join student s using(snummer)\n" . "where prjm_id={$prjm_id}" . " and snummer in" . $sqllate . "\n" . " order by afko,grp_num,achternaam,roepnaam";
echo "<div>\n";
?>
<table>
<tr>
<td>To mail to (almost) all students, select <input type='hidden' name='peerdata' value='prj_id_milestone'/>
<button name='checkAll' type='button' onclick='javascript:checkThem("snmailto[]")'>Select All</button></td>
<td> To mail to a few choose
<button name='checkNone' type='button' onclick='javascript:unCheckThem("snmailto[]")'>Select None</button></td>
</tr>
/**
* Get one (first) record.
* This method executes the query, returns the resultset pointing to the first record.
*
* @param $dbConn database connection to use
* @param $sql query string for this datum
* @return $resultSet: resultSet to use for any next items.
*/
function getFirstRecordSetFields($sql)
{
$resultSet = $this->Execute($sql);
if ($resultSet === false) {
$msg = $this->ErrorMsg();
echo "Cannot execute select statement \"" . $sql . "\", cause=" . $msg . "\n";
stacktrace(2);
exit;
}
if ($resultSet->EOF) {
die("cannot get data with query {$sql} because the set is empty");
} else {
// copy to arr
}
return $resultSet;
}
static function stackTrace() {
D::log(stacktrace(), 'Stack Trace');
}
/**
* Retreive the composed table.
* If as checkcolumn is present (checkbox >= 0) then a onload script will be loaded to the page header.
*/
public function getTable()
{
global $ADODB_FETCH_MODE;
$result = '';
$rowCount = 1;
$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
$coltypes = array();
$columnNames = array();
$resultSet = $this->dbConn->Execute($this->query);
if ($resultSet === false) {
$result .= "<pre style='color:800'>Cannot read table data with \n\t" . $this->query . " </pre>\n\treason \n\t" . $this->dbConn->ErrorMsg() . " at\n";
stacktrace(1);
$result .= "</pre>";
return $result;
}
$colcount = $resultSet->FieldCount();
$result .= $this->tabledef . "\n";
$result .= "<thead>\n";
if ($this->checkColumn >= 0 && $this->page != null) {
$this->page->addHeadText('
<script type="text/javascript">
function checkThem(ref,state){
var checks = document.getElementsByName(ref);
var boxLength = checks.length;
for ( i=0; i < boxLength; i++ ) {
checks[i].checked = state;
}
}</script>
');
$checkRow = "<tr style='background:rgba(255,128,0,0.4)'>";
if ($this->checkColumn > 0) {
$checkRow .= "<td colspan='" . $this->checkColumn . "'>";
}
$checkBox = "<input name='checkAll' type='checkbox' onclick='javascript:checkThem(\"" . $this->checkName . "\",this.checked)'/> (un)Check all";
$checkRow .= "<td> </td><td colspan='" . ($colcount - $this->checkColumn) . "'style='font-weight:bold;border:none'>{$checkBox}</td>";
$checkRow .= "</tr>\n";
$result .= $checkRow;
}
$result .= "<th>#</th>";
for ($i = 0; $i < $colcount; $i++) {
$field = $resultSet->FetchField($i);
$columnNames[$i] = $field->name;
$result .= "\t\t<th class='tabledata head' style='text-algin:left;'>" . niceName($field->name) . "</th>\n";
$columntypes[$i] = $resultSet->MetaType($i);
}
$result .= "</tr>\n</thead>\n<tbody>\n";
$oldValue = '';
$rowColor = $this->rainbow->restart();
if (!$resultSet->EOF) {
if ($this->colorChangerColumn >= 0 && isset($resultSet->fields[$this->colorChangerColumn])) {
$oldValue = $resultSet->fields[$this->colorChangerColumn];
}
}
while (!$resultSet->EOF) {
if ($this->colorChangerColumn >= 0 && isset($resultSet->fields[$this->colorChangerColumn]) && $oldValue != $resultSet->fields[$this->colorChangerColumn]) {
$rowColor = $this->rainbow->getNext();
$oldValue = $resultSet->fields[$this->colorChangerColumn];
}
$result .= "\t<tr style='background:{$rowColor}'>\n" . "<td align='right'>" . $rowCount++ . "</td>";
for ($i = 0, $max = $resultSet->FieldCount(); $i < $max; $i++) {
$val = isset($resultSet->fields[$i]) ? trim($resultSet->fields[$i]) : '';
if (substr($val, 0, 1) != '<') {
$val = $val;
}
if (substr($val, 0, 1) == '{' && substr($val, -1) == '}') {
$val = substr($val, 1, strlen($val) - 2);
$val = substr($val, 0, strlen($val) - 2);
$a = explode(',', $val);
$val = '<td>' . implode('</td><td>', $a) . '</td>';
}
$tdclass = 'tabledata';
switch ($columntypes[$i]) {
case 'int2':
case 'integer':
case 'numeric':
case 'float':
case 'real':
case 'N':
$tdclass .= ' num';
break;
default:
break;
}
$result .= "\t\t<td class='{$tdclass}'>" . $val . "</td>\n";
}
$result .= "\t</tr>\n";
$resultSet->MoveNext();
}
$result .= "</tbody>\n</table>\n";
$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
return $result;
}
function getSelectedData()
{
if ($this->dataCache != null) {
return $this->dataCache;
}
//echo "extra join <pre>{$this->extraJoin}</pre>";
$sql = "select 0 as first, pm.prj_id,pm.prjm_id,pm.milestone,p.year,trim(p.afko) as afko,trim(p.description) as description" . ",t.tutor as tutor_owner,p.valid_until,pm.assessment_due,trim(fc.course_short) as course_short\n" . " from prj_milestone pm join project p using(prj_id) join tutor t on(owner_id=userid) join fontys_course fc on (p.course=fc.course)\n" . ($this->extraJoin != '' ? "\njoin " . $this->extraJoin . "\n" : '') . " where pm.prjm_id=" . $this->prjm_id . ($this->whereClause != '' ? "\n and " . $this->whereClause . "\n" : '') . "\nunion\n" . "select 1 as first,pm.prj_id,pm.prjm_id,pm.milestone,p.year,trim(p.afko) as afko,trim(p.description) as description" . ",t.tutor as tutor_owner,p.valid_until,pm.assessment_due,trim(fc.course_short) as course_short\n" . " from prj_milestone pm join project p using(prj_id) join tutor t on(owner_id=userid) join fontys_course fc on (p.course=fc.course)\n" . ($this->extraJoin != '' ? "\njoin " . $this->extraJoin . "\n" : '') . ($this->whereClause != '' ? "\n where " . $this->whereClause . "\n" : '') . " order by first limit 1";
// echo "<pre style='color:#800;padding:2em'>{$sql}</pre>";
$resultSet = $this->dbConn->Execute($sql);
if ($resultSet === false) {
echo "<br>Cannot get project data with <pre>\"" . $sql . '"</pre>, cause ' . $this->dbConn->ErrorMsg() . "<br>";
stacktrace(1);
die;
}
if ($resultSet->EOF) {
return $this->nullResult;
} else {
$this->dataCache = $resultSet->fields;
}
return $this->dataCache;
}
function checkTable($dbConn, $query, $rowTriggerColumn, $headColumn, $checkcolumn, $notecolumn, $tabledef = "<table summary='simple table' border='1' style='border-collapse:collapse'>")
{
$result = '';
$head = "<tr>\n";
$triggerval = '';
global $ADODB_FETCH_MODE;
$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
$coltypes = array();
$colgroupdef = '<colgroup>' . "\n";
$columnNames = array();
$rowcounter = 0;
$resultSet = $dbConn->Execute($query);
if ($resultSet === false) {
echo "<pre>Cannot read table data with \n\t" . $query . " \n\treason \n\t" . $dbConn->ErrorMsg() . "at\n";
stacktrace(1);
echo "</pre>";
}
$colcount = $resultSet->FieldCount() - 1;
for ($i = 0; $i < $colcount; $i++) {
$field = $resultSet->FetchField($i);
$columnNames[$i] = $field->name;
if ($i != $headColumn && $i != $checkcolumn) {
$head .= "\t<th class='tabledata head' style='text-algin:left;'>" . niceName($field->name) . "</th>\n";
$colgroupdef .= "\t<col/>\n";
}
}
$sessioncount = 0;
$present = 0;
while (!$resultSet->EOF) {
if ($triggerval != $resultSet->fields[$rowTriggerColumn]) {
$rowcounter++;
if ($result != '') {
$result .= "<th align='right' >" . round(100 * $present / $sessioncount, 0) . "%</th></tr>\n";
$present = 0;
//$result .= "</tr><!-- new row -->\n";
}
$result .= "<tr>\n";
$colcount = $resultSet->FieldCount() - 1;
// get row head columns
for ($i = 0; $i < $colcount; $i++) {
if ($i != $headColumn && $i != $checkcolumn) {
$result .= "\t" . '<td>';
if (isset($resultSet->fields[$i])) {
$cell = trim($resultSet->fields[$i]);
} else {
$cell = ' ';
}
$result .= $cell;
$result .= "</td>\n";
}
}
}
$triggerval = $resultSet->fields[$rowTriggerColumn];
if ($rowcounter == 1) {
$sessioncount++;
$head .= "\t<th title='" . $resultSet->fields[$headColumn] . "'>{$sessioncount}</th>\n";
$colgroupdef .= "\t<col/>\n";
}
if (isset($resultSet->fields[$notecolumn])) {
$note = " title='" . $resultSet->fields[$notecolumn] . "' class='abs' ";
} else {
$note = '';
}
$result .= "\t<td{$note}>" . $resultSet->fields[$checkcolumn] . "</td>\n";
if (isset($resultSet->fields[$checkcolumn])) {
$present += $resultSet->fields[$checkcolumn] == 'P' ? 1 : 0;
}
// $result .= '<td>'.$resultSet->fields[1]."</td>\n";
$resultSet->MoveNext();
}
$result .= "<th align='right' >" . round(100 * $present / $sessioncount, 1) . "</th></tr>\n";
$head .= "<th>% P</th></tr><!-- /head -->\n";
$colgroupdef .= "</colgroup>\n";
$result .= "</table>\n";
$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
$result = $tabledef . "\n<thead>\n" . $colgroupdef . "\n</thead>\n" . $head . $result;
return $result;
}
function groupOpener($dbConn, $prjm_id, $isTutorOwner, $form_array)
{
// incoming prjm_id not used
if ($isTutorOwner && isset($form_array['openclose_candidate'])) {
$open_prjm_id = $form_array['open_prjm_id'];
$candidates = implode(",", $form_array['openclose_candidate']);
if (isset($form_array['opengrp'])) {
$openset = implode(",", $form_array['opengrp']);
} else {
$openset = '(0)';
}
$sql = "begin work;\n";
if (isset($form_array['opengrp'])) {
$openset = implode(",", $form_array['opengrp']);
$sql .= "INSERT INTO assessment (contestant,judge,criterium,grade,prjtg_id)\n" . " SELECT contestant,judge,criterium,grade,prjtg_id\n" . " from assessment_builder3 ab where (contestant,judge,criterium,prjtg_id) not in\n" . " (select distinct contestant,judge,criterium,prjtg_id \n" . " from assessment where prjtg_id in (select prjtg_id from prj_tutor pt where pt.prjm_id={$prjm_id} ))\n" . " and ab.prjm_id={$prjm_id};\n" . "update prj_tutor set prj_tutor_open=true,assessment_complete=false where prjtg_id in ({$openset});\n" . "update prj_grp set prj_grp_open=true where prjtg_id in ({$openset});\n" . "update prj_grp set prj_grp_open=false where prjtg_id in ({$candidates}) and prjtg_id not in ({$openset});\n" . "update prj_tutor set prj_tutor_open=false where prjtg_id in ({$candidates}) and prjtg_id not in ({$openset});\n" . "update prj_milestone set has_assessment=true where prjm_id={$prjm_id};\n";
} else {
$sql .= "update prj_tutor set prj_tutor_open=false where prjtg_id in ({$candidates});\n" . "update prj_grp set prj_grp_open=false where prjtg_id in ({$candidates});\n";
}
$sql .= "update prj_milestone pm set prj_milestone_open=(select not should_close as open from should_close_prj_milestone where prjm_id=pm.prjm_id)" . " where prjm_id={$open_prjm_id};\n" . "commit;";
//$dbConn->log($sql);
$resultSet = $dbConn->Execute($sql);
if ($resultSet === false) {
echo "<br>Cannot update grp open/close with <pre>{$sql}</pre>, cause" . $dbConn->ErrorMsg() . "<br>";
stacktrace(1);
die;
}
}
}
