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