} $subSelect = $cols . "\n from" . $join; // $dbConn->log($subSelect); } $grades = ""; $con = ''; foreach ($gradeColumns as $grade) { $grades .= $con . "subselect.{$grade},subselect.{$grade}_mul"; $con = "\n\t,"; } $sql = "select snummer,achternaam,roepnaam,voorvoegsel, afko, year, \n" . "milestone,grp_num,\"alias\",tutor,\"role\",{$grades},sw.final_grade \n" . "from prj_grp join all_prj_tutor using(prjtg_id)\n" . " join student using(snummer) \n" . " left join milestone_grade using(prjm_id,snummer)\n" . " left join student_role using (prjm_id,snummer)\n" . " left join project_roles using (prj_id,rolenum)\n" . " join ({$subSelect}) subselect using(snummer)\n" . " left join (select snummer,round(grade_weight_sum/w.weight_sum,1) \n" . " as final_grade from project_grade_weight_sum_product\n" . " cross join (select weight_sum from project_weight_sum where prj_id={$prj_id}) w where prj_id={$prj_id} ) sw using(snummer)\n" . "where prjm_id={$prjm_id} order by grp_num,achternaam,roepnaam"; $pp = array(); $dbConn->log($sql); $spreadSheetWriter = new SpreadSheetWriter($dbConn, $sql); $title = "Results for all participants in project {$afko} {$year} milestone {$milestone}"; $spreadSheetWriter->setFilename($filename)->setLinkUrl($server_url . $PHP_SELF)->setTitle($title)->setColorChangerColumn(7); //->setRowParser( new RowWithArraysParser()); $spreadSheetWriter->processRequest(); $pp['spreadSheetWidget'] = $spreadSheetWriter->getWidget(); $page = new PageContainer(); $page->setTitle('Get group tables'); $page_opening = "Group lists for project {$afko} {$description} <span style='font-size:8pt;'>prjm_id {$prjm_id} prj_id {$prj_id} milestone {$milestone} </span>"; $nav = new Navigation($tutor_navtable, basename($PHP_SELF), $page_opening); $nav->setInterestMap($tabInterestCount); $rainbow = new RainBow(); $pp['rtable'] = getQueryToTableChecked($dbConn, $sql, false, 7, $rainbow, -1, '', ''); $pp['selector'] = $prjSel->getSelector(); $pp['selectionDetails'] = $prjSel->getSelectionDetails(); $page->addBodyComponent($nav); $page->addHtmlFragment('templates/milestonegrades.html', $pp); $page->show();
$query_comment = $_REQUEST['query_comment']; $query_text = $_REQUEST['query_text']; $query_name_s = pg_escape_string($_REQUEST['query_name']); $query_comment_s = pg_escape_string($_REQUEST['query_comment']); $query_text_s = pg_escape_string($_REQUEST['query_text']); $save_query = "begin work;\n" . "update any_query set active = false where owner={$peer_id} and query_name='{$query_name_s}';\n" . "insert into any_query(owner,query_name,query_comment,query)\n" . "values({$peer_id},'{$query_name_s}','{$query_comment_s}','{$query_text_s}');" . "\ncommit;"; $dbConn->Execute($save_query); } if (isset($_REQUEST['delete_query']) && preg_match('/^\\d+$/', $_REQUEST['delete_query'])) { $dquery = $_REQUEST['delete_query']; $delete_query = "delete from any_query where owner={$peer_id} and any_query_id={$dquery}"; $dbConn->Execute($delete_query); } $spreadSheetWriter = new SpreadSheetWriter($dbConn, $expanded_query); $filename = 'anyquery' . date('Y-m-d-H-i'); $spreadSheetWriter->setFilename($filename)->setTitle("Query {$query_name} ({$peer_id}) {$fdate}")->setLinkUrl($server_url . $PHP_SELF)->setAutoZebra(true); $spreadSheetWriter->processRequest(); $spreadSheetWidget = $spreadSheetWriter->getWidget(); $scripts = '<script type="text/javascript" src="js/jquery.js"></script> <script src="js/jquery.tablesorter.js"></script> <script type="text/javascript"> $(document).ready(function() { $("#myTable").tablesorter({widgets: [\'zebra\']}); }); </script> <link rel=\'stylesheet\' type=\'text/css\' href=\'' . SITEROOT . '/style/tablesorterstyle.css\'/> '; pagehead2('Execute and sql query to the database', $scripts); $page_opening = "Execute a query"; $nav = new Navigation($tutor_navtable, basename($PHP_SELF), $page_opening);
</script> <link rel=\'stylesheet\' type=\'text/css\' href=\'' . SITEROOT . '/style/tablesorterstyle.css\'/> '; $csvout = 'N'; $csvout_checked = ''; if (isset($_REQUEST['csvout'])) { $csvout = $_REQUEST['csvout']; $csvout_checked = $csvout == 'Y' ? 'checked' : ''; } // <a href='../emailaddress.php?snummer=snummer'>snummer</a> $fdate = date('Ymd-Hi'); $filename = 'jaag_buit-' . $fdate; $sql = "select * from jaag_buit_html"; $sqlcsv = "select * from jaag_buit"; $spreadSheetWriter = new SpreadSheetWriter($dbConn, $sqlcsv); $spreadSheetWriter->setFilename($filename)->setTitle("Jaag buit {$fdate}")->setLinkUrl($server_url . $PHP_SELF)->setFilename($filename)->setAutoZebra(true); $spreadSheetWriter->processRequest(); $spreadSheetWidget = $spreadSheetWriter->getWidget(); pagehead2('Jaag Buit', $scripts); $page_opening = "Resultaat van jaagactie tot nu toe"; $nav = new Navigation($tutor_navtable, basename($PHP_SELF), $page_opening); echo $nav->show(); ?> <div id='navmain' style='padding:1em;'> <fieldset><legend>Select output type</legend> <form method="get" name="project" action="<?php echo $PHP_SELF; ?> "> <input type='submit' name='get' value='Get' /> Excel file:
extract($_SESSION); $oldClassSelector = hoofdgrpSelector($dbConn, 'hoofdgrp', $hoofdgrp); if (isset($hoofdgrp)) { $sql = "select trim(f.faculty_short) as faculty_short,trim(hoofdgrp) as hoofdgrp\n" . " from hoofdgrp_s h join faculty f using(faculty_id) where hoofdgrp='{$hoofdgrp}'"; $resultSet = $dbConn->Execute($sql); if ($resultSet !== false) { extract($resultSet->fields); } // $dbConn->log($sql); } $fdate = date('Y-m-d'); $sqlhead = "select distinct snummer," . "achternaam||rtrim(coalesce(', '||voorvoegsel,'')::text) as achternaam ,roepnaam, " . "pcn,gebdat as birth_date,t.tutor as slb,rtrim(email1) as email1," . "studieplan_short as studieplan,sclass,hoofdgrp ,\n" . "straat,huisnr,plaats,phone_gsm,phone_home\n" . "from \n"; $sqltail = " join student_class using(class_id) left join tutor t on (s.slb=t.userid)\n" . " left join studieplan using(studieplan)\n" . " left join faculty f on(f.faculty_id=s.faculty_id)\n" . "where hoofdgrp='{$hoofdgrp}' order by achternaam,roepnaam\n"; $spreadSheetWriter = new SpreadSheetWriter($dbConn, $sqlhead . ' student s ' . $sqltail); $filename = 'hoofdgrp_list_' . $faculty_short . '_' . $hoofdgrp . '-' . date('Y-m-d'); $spreadSheetWriter->setFilename($filename)->setTitle("Hoofd groep list {$faculty_short} {$hoofdgrp} {$fdate}")->setLinkUrl("{$server_url}{$PHP_SELF}?hoofdgrp={$hoofdgrp}")->setFilename($filename)->setAutoZebra(true); $spreadSheetWriter->processRequest(); $spreadSheetWidget = $spreadSheetWriter->getWidget(); $sqlhead = "select distinct '<a href=''student_admin.php?snummer='||snummer||'''target=''_blank''>'||snummer||'</a>' as snummer,\n" . "'<img src='''||photo||''' style=''height:24px;width:auto;''/>' as foto,\n" . "achternaam||rtrim(coalesce(', '||voorvoegsel,'')::text) as achternaam ,roepnaam, \n" . "pcn,cohort,t.tutor as slb,gebdat as birth_date,rtrim(email1) as email1,\n" . "studieplan_short as studieplan,faculty_short as facul,sclass,hoofdgrp,\n" . "straat,huisnr,plaats,phone_gsm,phone_home\n" . " from \n"; $sql2 = $sqlhead . ' student_email s natural join portrait ' . $sqltail; //$dbConn->log($sql2); $scripts = '<script type="text/javascript" src="js/jquery.js"></script> <script src="js/jquery.tablesorter.js"></script> <script type="text/javascript"> $(document).ready(function() { $("#myTable").tablesorter({widgets: [\'zebra\']}); }); </script> <link rel=\'stylesheet\' type=\'text/css\' href=\'' . SITEROOT . '/style/tablesorterstyle.css\'/> ';
<?php require_once './peerlib/peerutils.inc'; require_once 'queryToXlsx.php'; require_once 'SpreadSheetWriter.php'; require_once 'pgrowparser.php'; $_REQUEST['filetype'] = 'Excel2007'; $prjm_id = 266; $criteria = criteriaShortAsArray(getCriteria($prjm_id), 'en'); $criteria[] = 'Overall'; $sql = "select prjtg_id from prj_tutor where prjm_id={$prjm_id}"; $sql2 = ""; $resultSet = $dbConn->Execute($sql); $con = " "; while (!$resultSet->EOF) { extract($resultSet->fields); $sql2 .= $con . " select * from assessment_grade_set({$prjtg_id},6.5)\n"; $con = "union\n\t"; $resultSet->moveNext(); } //echo "<pre>$sql2</pre>\n"; $sqlt = "select s.snummer,achternaam,roepnaam,voorvoegsel,gebdat,grp_num,grade\n" . " from ({$sql2}) ags \n" . " join prj_grp using(prjtg_id,snummer)" . " join all_prj_tutor using(prjtg_id) \n" . " join student s using(snummer) order by grp_num,achternaam"; //echo "<pre>$sqlt</pre>\n"; global $ADODB_FETCH_MODE; $ADODB_FETCH_MODE = ADODB_FETCH_NUM; $spreadSheetWriter = new SpreadSheetWriter($dbConn, $sqlt); $headers = array('snummer', 'achternaam', 'roepnaam', 'voorvoegsel', 'gebdat', 'grp_num', $criteria); $filename = 'groupresult' . date('Y-m-d'); $c = array('FFC0C0FF', 'FFFFFFFF'); $spreadSheetWriter->setFilename($filename)->setTitle("Group result")->setLinkUrl("http://www.fontysvenlo.org")->setColorChangerColumn(5)->setFilename($filename)->setRowParser(new RowWithArraysPreHeadersParser($headers)); $spreadSheetWriter->processRequest();
$criteria = criteriaShortAsArray(getCriteria($prjm_id), 'en'); $criteria[] = 'Overall'; $sql = "select prjtg_id,tutor_grade from prj_tutor where prjm_id={$prjm_id}"; $sql2 = ""; $resultSet = $dbConn->Execute($sql); $con = " "; while (!$resultSet->EOF) { extract($resultSet->fields); $sql2 .= $con . " select * from assessment_grade_set({$prjtg_id},{$tutor_grade})\n"; $con = "union\n\t"; $resultSet->moveNext(); } //echo "<pre>$sql2</pre>\n"; $sql = "select s.snummer,achternaam,roepnaam,voorvoegsel,gebdat,grp_num,coalesce(mg.grade,tutor_grade) as group_grade," . "ags.grade,\n" . "ags.multiplier,ags.multiplier[array_upper(ags.multiplier,1)] as final_mult," . "ags.grade[array_upper(ags.grade,1)] as peers_grade,tutor,mg.grade as final_tutor_grade,operator,date_trunc('second',ts) as trans_time\n" . " from \n" . " prj_grp \n " . " join all_prj_tutor using(prjtg_id) \n" . " join student s using(snummer)\n " . "left join ({$sql2}) ags using(prjtg_id,snummer)\n" . " left join milestone_grade mg using(snummer,prjm_id) left join transaction using(trans_id)\n" . " where prjm_id={$prjm_id} order by grp_num,achternaam"; $spreadSheetWriter = new SpreadSheetWriter($dbConn, $sql); $title = "Results for all peer assessment groups in project {$afko} {$year} milestone {$milestone}"; $spreadSheetWriter->setFilename($filename)->setLinkUrl($server_url . $PHP_SELF)->setTitle($title)->setColorChangerColumn(5)->setRowParser(new RowWithArraysParser()); $spreadSheetWriter->processRequest(); $pp['spreadSheetWidget'] = $spreadSheetWriter->getWidget(); $pp['projectSelector'] = $prjSel->getSelector(); $pp['selectionDetails'] = $prjSel->getSelectionDetails(); $page = new PageContainer(); $page->setTitle('All group tables'); $page_opening = "Group peer assessment results for all groups of project {$afko} {$description} <span style='font-size:8pt;'>prjm_id {$prjm_id} prj_id {$prj_id} milestone {$milestone} </span>"; $nav = new Navigation($tutor_navtable, basename($PHP_SELF), $page_opening); $nav->setInterestMap($tabInterestCount); $rainbow = new RainBow(); $pp['rtable'] = getQueryToTableChecked($dbConn, $sql, false, 5, $rainbow, -1, '', ''); $page->addBodyComponent($nav); $page->addHtmlFragment('templates/allgroupresult.html', $pp); $page->show();
$sql = "select trim(f.faculty_short) as faculty_short,trim(hoofdgrp) as hoofdgrp\n" . " from hoofdgrp_s h join faculty f using(faculty_id) where hoofdgrp='{$hoofdgrp}'"; $resultSet = $dbConn->Execute($sql); if ($resultSet !== false) { extract($resultSet->fields); } // $dbConn->log($sql); } $fdate = date('Y-m-d'); $sql = "select tutor from tutor where userid={$slb}"; $rs = $dbConn->Execute($sql); $tutorCode = $rs->fields['tutor']; $sqlhead = "select distinct snummer," . "achternaam||rtrim(coalesce(', '||voorvoegsel,'')::text) as achternaam ,roepnaam, " . "pcn,gebdat as birth_date,t.tutor as slb,rtrim(email1) as email1," . "studieplan_short as studieplan,cohort,sclass,hoofdgrp ,\n" . "straat,huisnr,plaats,phone_gsm,phone_home,sort1\n" . "from \n"; $sqltail = " join student_class using(class_id) left join tutor t on (s.slb=t.userid)\n" . " left join studieplan using(studieplan)\n" . " left join faculty f on(f.faculty_id=s.faculty_id)\n" . "where slb='{$slb}' and snummer not in (select userid from tutor) order by cohort desc,sclass,achternaam,roepnaam\n"; $spreadSheetWriter = new SpreadSheetWriter($dbConn, $sqlhead . ' student s ' . $sqltail); $filename = 'slb_list_' . $faculty_short . '_' . $tutorCode . '-' . date('Y-m-d'); $spreadSheetWriter->setFilename($filename)->setTitle("Slb groep list {$faculty_short} {$tutorCode} {$fdate}")->setLinkUrl($server_url . $PHP_SELF)->setFilename($filename)->setAutoZebra(true); $spreadSheetWriter->processRequest(); $spreadSheetWidget = $spreadSheetWriter->getWidget(); $sqlhead = "select distinct '<a href=''student_admin.php?snummer='||snummer||'''target=''_blank''>'||snummer||'</a>' as snummer,\n" . "'<img src='''||photo||''' style=''height:24px;width:auto;''/>' as foto,\n" . "achternaam||rtrim(coalesce(', '||voorvoegsel,'')::text) as achternaam ,roepnaam, \n" . "pcn,cohort,t.tutor as slb,gebdat as birth_date,rtrim(email1) as email1,\n" . "studieplan_short as studieplan,faculty_short as facul,sclass,hoofdgrp,\n" . "straat,huisnr,plaats,phone_gsm,phone_home\n" . " from \n"; $sql2 = $sqlhead . ' student_email s natural join portrait ' . $sqltail; //$dbConn->log($sql2); $scripts = '<script type="text/javascript" src="js/jquery.js"></script> <script src="js/jquery.tablesorter.js"></script> <script type="text/javascript"> $(document).ready(function() { $("#myTable").tablesorter({widgets: [\'zebra\']}); }); </script> <link rel=\'stylesheet\' type=\'text/css\' href=\'' . SITEROOT . '/style/tablesorterstyle.css\'/> ';
requireCap(CAP_TUTOR); $prj_id = 1; $milestone = 1; $prjm_id = 0; extract($_SESSION); $prjSel = new PrjMilestoneSelector2($dbConn, $peer_id, $prjm_id); extract($prjSel->getSelectedData()); $_SESSION['prj_id'] = $prj_id; $_SESSION['prjm_id'] = $prjm_id; $_SESSION['milestone'] = $milestone; //$dbConn->log("$tutor_code $prjm_id, $prj_id, $milestone"); $filename = 'examlist_' . $afko . '-' . date('Ymd'); $sql = "select distinct snummer as id,achternaam as surname,roepnaam||coalesce(' '||voorvoegsel,'') as name,trim(sclass) as sclass,voorletters\n" . " ,upper(lang) as lang,prjm_id,trim(coalesce(alias,'g'||grp_num)) as alias, md5(prjm_id::text||prj_grp.snummer::text || now()) AS token, \n" . " cohort,email1,course_short as education, case when prjm_id=422 then 1 else 0 end as lo" . " from student s join prj_grp using(snummer) \n" . " join all_prj_tutor using(prjtg_id)\n" . " join student_class using(class_id) left join fontys_course fc on(s.opl=fc.course)" . " where prjm_id in (422,{$prjm_id}) order by lo,surname,name"; $spreadSheetWriter = new SpreadSheetWriter($dbConn, $sql); $title = "exam list"; $spreadSheetWriter->setFilename($filename)->setLinkUrl($server_url . $PHP_SELF . '?prjm_id=' . $prjm_id)->setTitle($title)->setAutoZebra(true); $spreadSheetWriter->processRequest(); $spreadSheetWidget = $spreadSheetWriter->getWidget(); // <a href='../emailaddress.php?snummer=snummer'>snummer</a> $rainbow = new RainBow(STARTCOLOR, COLORINCREMENT_RED, COLORINCREMENT_GREEN, COLORINCREMENT_BLUE); pagehead('Get exam list'); $page_opening = "Exam list for project {$afko} {$description} (prj_id {$prj_id} milestone {$milestone} prjm_id {$prjm_id})"; $nav = new Navigation($tutor_navtable, basename($PHP_SELF), $page_opening); $nav->setInterestMap($tabInterestCount); $prjSel->setJoin('milestone_grp using (prj_id,milestone)'); $prj_id_selector = $prjSel->getSelector(); echo $nav->show(); ?> <div id='navmain' style='padding:1em;'> <fieldset><legend>Select project</legend> <form method="get" name="project" action="<?php
$prj_id = 1; $milestone = 1; $prjm_id = 0; extract($_SESSION); $prjSel = new PrjMilestoneSelector2($dbConn, $peer_id, $prjm_id); extract($prjSel->getSelectedData()); $_SESSION['prj_id'] = $prj_id; $_SESSION['prjm_id'] = $prjm_id; $_SESSION['milestone'] = $milestone; $filename = 'grouplist_' . $afko . '-' . date('Ymd'); $title = "Student and groups in project {$afko} milestone {$milestone}"; $sqlhead = "select s.snummer as snummer,"; // <a href='../student_admin.php?snummer=snummer'>snummer</a> $sqltail = "achternaam||rtrim(coalesce(', '||voorvoegsel,'')) as achternaam ,roepnaam, pcn,gebdat as birth_date,cohort,\n" . "role,slb.tutor as slb,rtrim(email1) as email1,rtrim(email2) as email2,c.sclass as klas,afko,milestone," . "sp.studieplan_short as studplan,\n" . "apt.grp_num,ga.alias,apt.grp_name,apt.tutor,'' as grade\n" . "from prj_grp pg join student s using (snummer)\n" . " join all_prj_tutor apt on(pg.prjtg_id=apt.prjtg_id)\n" . "left join studieplan sp using(studieplan)\n" . "left join student_class c using (class_id)\n" . "left join student_role sr on(apt.prjm_id=sr.prjm_id and pg.snummer=sr.snummer)\n" . "left join project_roles pr on(pr.prj_id=apt.prj_id and sr.rolenum=pr.rolenum)\n" . "left join grp_alias ga on(apt.prjtg_id=ga.prjtg_id)\n" . "left join alt_email aem on(aem.snummer=pg.snummer) \n" . "left join tutor slb on (s.slb=slb.userid) \n" . "where apt.prjm_id={$prjm_id} order by grp_num,achternaam,roepnaam"; $spreadSheetWriter = new SpreadSheetWriter($dbConn, $sqlhead . $sqltail); $spreadSheetWriter->setFilename($filename)->setLinkUrl($server_url . $PHP_SELF . '?prjm_id=' . $prjm_id)->setTitle($title)->setAutoZebra(false)->setColorChangerColumn(14); $spreadSheetWriter->processRequest(); $spreadSheetWidget = $spreadSheetWriter->getWidget(); $sqlhead = "select distinct '<a href=\"student_admin.php?snummer='||s.snummer||'\" target=\"_blank\">'||s.snummer||'</a>' as snummer,"; $rainbow = new RainBow(STARTCOLOR, COLORINCREMENT_RED, COLORINCREMENT_GREEN, COLORINCREMENT_BLUE); $scripts = '<script type="text/javascript" src="js/jquery.js"></script> <script src="js/jquery.tablesorter.js"></script> <script type="text/javascript"> $(document).ready(function() { // do stuff when DOM is ready $("#myTable").tablesorter({ }); }); </script> <link rel=\'stylesheet\' type=\'text/css\' href=\'' . SITEROOT . '/style/tablesorterstyle.css\'/> ';
$resultSet = $dbConn->Execute($sql); if ($resultSet === false) { die("<br>Cannot get project name with \"" . $sql . '", cause ' . $dbConn->ErrorMsg() . "<br>"); } if (!$resultSet->EOF) { extract($resultSet->fields); } $filename = 'groupresult' . trim($afko) . ($grp_num == '*' ? '_' : $grp_num) . 'm' . $milestone . '_consolidated_' . $fdate; $title = "Group result project {$afko} {$year}, milestone {$milestone} group {$grp_num} "; if (isset($alias)) { $title .= " ({$alias})"; } $title .= "; spreadsheet file created on {$fdate}"; $sqlt = "select s.snummer as contestant, " . "roepnaam||' '||coalesce(voorvoegsel,'')||' '||achternaam as naam, " . "ags.grade as peerg,\n" . "ags.multiplier[array_upper(ags.multiplier,1)] as grp_multiplier, " . "coalesce(round(mg.grade,2),round({$productgrade}*ags.multiplier[array_upper(ags.multiplier,1)],2)) as tutorg \n" . " from student s join assessment_grade_set({$prjtg_id},{$productgrade}) ags using (snummer)" . " join all_prj_tutor using(prjtg_id) " . " left join milestone_grade mg using(prjm_id,snummer) order by achternaam,roepnaam,snummer"; $spreadSheetWriter = new SpreadSheetWriter($dbConn, $sqlt); $spreadSheetWriter->setFilename($filename)->setLinkUrl($server_url . $PHP_SELF . '?class_id=' . $class_id)->setTitle($title)->setAutoZebra(true)->setWeights(array(1, 2, 3, 4))->setFirstWeightsColumn(2)->setWeightSumsColumn(9)->setRowParser(new RowWithArraysPreHeadersParser(array('contestant', 'name', criteriaShortAsArray($criteria, 'en'), 'multiplier', 'tutorg'))); $spreadSheetWriter->processRequest(); $scripts = '<script type="text/javascript" src="js/jquery.js"></script> <script src="js/jquery.tablesorter.js"></script> <script type="text/javascript"> $(document).ready(function() { // do stuff when DOM is ready $("#groupresult").tablesorter({ }); }); </script> <link rel=\'stylesheet\' type=\'text/css\' href=\'' . SITEROOT . '/style/tablesorterstyle.css\'/> '; pagehead2('groupresult', $scripts); $prj_id_selector = $prjSel->getSelector(); $prj_data = $prjSel->getSelectionDetails();