Modified Live Side Student Free Periods Report
Forums:
~[if#pageaction.~[gpv:action]#getresults]
<!DOCTYPE html>
<html>
<!--[CRM BEGIN]-->
<title>Student Free Report (Live Side)</title>
<meta name="author" content="crb-core" />
<meta name="description" content="(Live Scheduling) Find all periods where a student in this school does not have a course on a given day." />
<meta name="version" content="3.3.0" />
<meta name="schoolreport" />
<!--[CRM END]-->
<link href="/images/css/screen.css" rel="stylesheet" media="screen">
<link href="/images/css/print.css" rel="stylesheet" media="print">
<link rel="stylesheet" href="/images/css/theme/crm-css/jquery-ui.custom.css" type="text/css">
<link rel="stylesheet" href="/admin/includes/template/reports-common.css" type="text/css" media="all">
<link rel="stylesheet" href="/admin/includes/template/reports-printable.css" type="text/css" media="print">
~[wc:commonscripts]
~[x:insertfile;/scripts/custom-jquery-1.0.html]
~[x:insertfile;/scripts/custom-datatables-1.0.html]
~[x:insertfile;/admin/includes/template/reports-commonscripts.html]
<script type="text/javascript">
var reportdataURL ='/~[self]';
var pageseperator ='<hr class="noprint pageseperator" /><div class="pbreak"><span style="display: none"> </span></div>';
var totalreports = 0;
$(function(){
$('#params_container').accordion({collapsible:true,autoHeight:false});
$('input:submit').button({label:"Submit"});
$('.datepicker').datepicker({
dateFormat: convertOracleDF('~[gpv:dateformat]')
});
$('#ui-datepicker-div').hide();//Bug in jQuery UI 1.8.9 causes datepicker div to be visible upon creation
$('#params').submit(function(){
clearError();
$("#reportresult").empty();
$("#reportresult").html('<span id="loader">Loading <img src="' +$("#busyimage").attr("src")+'" /></span>');
var settings = $('#params').serializeObject();
$.get(reportdataURL, settings
)
.success(processReportResults)
.error(function(xhr, status, error){
$('#loader').hide();
displayError("Report failed to load. Status Message: " +status);
});
return false; //override regular form submission
});
});//end jquery.ready
function processReportResults(data){
$('#reportresult').html(data);
$('#reportresult table').addClass('display').dataTable({
"bPaginate": false,
"bFilter": false,
"bJQueryUI": true,
"sDom": 'T<"H"fr>t<"F"ip>',
"oTableTools": {
"sSwfPath":"/scripts/tabletools/swf/copy_cvs_xls_pdf.swf"
}
});
}
function displayError(msg){
$('#error_container').html('<div id="alertmsg" style="padding: 0pt 0.7em;" class="ui-state-error ui-corner-all"><p><span style="float: left; margin-right: 0.3em;" class="ui-icon ui-icon-alert"></span><strong>Alert: </strong>'+msg+'</p></div>').show();
}
function clearError(){
$('#error_container').empty().hide();
}
</script>
</head>
<body style="margin:12px;" bgcolor="#ffffff">
~[x:insertfile;/admin/includes/template/admin_reportheader_start.html]
Student Free Report (Live Side)
~[x:insertfile;/admin/includes/template/admin_reportheader_end.html]
<!-- Begin content -->
<img id="busyimage" style="display:none;" src="/images/busy_blue.gif" />
<div id="reportcontent" class="rounded_block">
<h2 class="noprint">Student Free Report (Live Side)</h2>
<!-- Begin report parameters-->
<form class="noprint" name="params" id="params" method="GET" action="#" target="_blank">
<div id="params_container" class="noprint">
<h3><a href="#">Report Parameters</a></h3>
<div id="paramsinner_container">
<table border="0" cellspacing="0" cellpadding="4">
<tr class="headerrow">
<td class="bold">Term Start Date</td>
<td class="bold">Student</td>
<td class="bold">Grade</td>
<td class="bold">Team</td>
<td class="bold">House</td>
<td class="bold">Period</td>
<td class="bold">Day in Rotation</td>
</tr>
<tr class="headerrow">
<td>
<select name="trm">
~[tlist_sql;
SELECT to_char(t2.firstday,'mm/dd/yyyy'), 'Any Term'||' ('||to_char(t2.firstday,'mm/dd/yyyy')||' - '||to_char(t.lastday,'mm/dd/yyyy')||')' as term
FROM terms t
JOIN terms t2 on t.schoolid = t2.schoolid and t.yearid = t2.yearid and t2.abbreviation = 'S1'
WHERE t.SchoolID = ~(curschoolid)
AND t.yearid = ~(curyearid)
AND t.isyearrec = 1
]
<option value="~(firstday;t)">~(term;t)</option>
[/tlist_sql]
~[tlist_sql;
SELECT to_char(t.firstday,'mm/dd/yyyy'), t.abbreviation||' ('||to_char(t.firstday,'mm/dd/yyyy')||' - '||to_char(t.lastday,'mm/dd/yyyy')||')' as term
FROM terms t
WHERE t.SchoolID = ~(curschoolid)
AND t.yearid = ~(curyearid)
AND t.abbreviation in ('S1','S2')
ORDER BY t.abbreviation]
<option value="~(firstday;t)">~(term;t)</option>
[/tlist_sql]
</select>
</td>
<td>
<select name="sid">
<option value="-1">Any Student
~[tlist_sql;
SELECT ID, LastFirst
FROM Students
WHERE SchoolID = ~(curschoolid)
ORDER BY LastFirst]
<option value="~(ID;l)" >~(Student;t)</option>
[/tlist_sql]
</select>
</td>
<td>
<select name="gr">
<option value="-1" selected="selected">Any Grade</option>
~[tlist_sql;
SELECT DISTINCT Grade_Level
FROM Students
WHERE SchoolID = ~(curschoolid)
ORDER BY Grade_Level]
<option value="~(gr;l)">~(gr;l)</option>
[/tlist_sql]
</select>
</td>
<td>
<select name="tid">
<option value="-1" selected="selected">Any Team</option>
~[tlist_sql;
SELECT DISTINCT Team
FROM Students
WHERE SchoolID = ~(curschoolid)
ORDER BY Team]
<option value="~(Team;t)">~(Team;t)</option>
[/tlist_sql]
</select>
</td>
<td>
<select name="hid">
<option value="-1" selected="selected">Any House</option>
~[tlist_sql;
SELECT DISTINCT House
FROM Students
WHERE SchoolID = ~(curschoolid)
ORDER BY House]
<option value="~(House;t)" >~(House;t)</option>
[/tlist_sql]
</select>
</td>
<td>
<select name="pid">
<option value="-1" selected="selected">Any Period</option>
~[tlist_sql;
SELECT Abbreviation, Name
FROM Period
WHERE SchoolID = ~(curschoolid) AND Year_ID = ~(curyearid)
ORDER BY Sort_Order]
<option value="~(Abbreviation;t)" >~(PeriodName;t)</option>
[/tlist_sql]
</select>
</td>
<td>
<select name="did">
<option value="-1" selected="selected">Any Day</option>
~[tlist_sql;
SELECT Letter
FROM Cycle_Day
WHERE SchoolID = ~(curschoolid) AND Year_ID = ~(curyearid)
ORDER BY SortOrder]
<option value="~(Letter;t)">~(Letter;t)</option>
[/tlist_sql]
</select>
</td>
</tr>
</table>
<input type="hidden" name="dothisfor" value="selected" />
<input type="hidden" name="action" value="getresults" />
<input type="hidden" name="dateformat" value="~[gpv:dateformat]" />
<input value="Submit" type="submit" />
</div>
</div>
</form>
<!-- End report parameters-->
<div id="error_container"></div>
<div id="reportresult" class="rounded_block">Waiting for report parameters.</div>
</div>
<!-- end content -->
~[x:insertfile;/admin/includes/template/pds_copyright_footer.html]
</body>
</html>
[else#pageaction]
<table border="1">
<thead>
<tr class="headerRow">
<th>Student</th>
<th>Grade</th>
<th>Team</th>
<th>House</th>
<th>Period</th>
<th>Day</th>
</tr>
</thead>
<tbody>
~[tlist_sql;
SELECT s.DCID, s.LastFirst, s.Grade_Level, s.Team, s.House, dr.Abbreviation, dr.Letter
FROM Students s INNER JOIN
(SELECT cyd.Letter, p.Period_Number, p.Abbreviation, cyd.SchoolID, cyd.Year_ID
FROM Cycle_Day cyd INNER JOIN Period p ON cyd.SchoolID = p.SchoolID AND cyd.Year_ID = p.Year_ID
WHERE cyd.SchoolID = ~(curschoolid) AND cyd.Year_ID = ~(curyearid)) dr ON s.SchoolID = dr.SchoolID
WHERE to_date('~[gpv:trm]','~[gpv:dateformat]') BETWEEN s.EntryDate AND s.ExitDate
AND (dr.Letter || ' _ ' || to_char(dr.Period_Number)) NOT IN
(SELECT secm.Cycle_Day_Letter || ' _ ' || to_char(secm.Period_Number)
FROM CC INNER JOIN Section_Meeting secm ON secm.SectionID = abs(cc.SectionID)
WHERE cc.StudentID = s.ID AND cc.SchoolID = dr.SchoolID AND secm.Year_ID = dr.Year_ID
AND to_date('~[gpv:trm]','~[gpv:dateformat]') BETWEEN cc.DateEnrolled AND cc.DateLeft
)
AND to_char(s.Grade_Level) LIKE CASE WHEN to_char('~[gpv:gr]') = '-1' THEN '%' ELSE to_char('~[gpv:gr]') END
AND UPPER(CASE WHEN s.Team is null THEN ' ' ELSE s.Team END) LIKE CASE WHEN to_char('~[gpv:tid]') = '-1' THEN '%' ELSE UPPER(to_char('~[gpv:tid]')) END
AND UPPER(CASE WHEN s.House is null THEN ' ' ELSE s.House END) LIKE CASE WHEN to_char('~[gpv:hid]') = '-1' THEN '%' ELSE UPPER(to_char('~[gpv:hid]')) END
AND dr.Abbreviation LIKE CASE WHEN to_char('~[gpv:pid]') = '-1' THEN '%' ELSE to_char('~[gpv:pid]') END
AND dr.Letter LIKE CASE WHEN to_char('~[gpv:did]') = '-1' THEN '%' ELSE to_char('~[gpv:did]') END
AND to_char(s.ID) LIKE CASE WHEN to_char('~[gpv:sid]') = '-1' THEN '%' ELSE to_char('~[gpv:sid]') END
ORDER BY s.LastFirst, dr.Period_Number, dr.Letter
;alternatecolor]
<tr class="oddrow">
<td><a href="/admin/students/home.html?frn=001~(DCID;l)" target="_blank">~(Student;t)</a> </td>
<td>~(Grade;l) </td>
<td>~(Team;t) </td>
<td>~(House;t) </td>
<td>~(Period;t) </td>
<td>~(Day;t) </td>
</tr>
[/tlist_sql]
</tbody>
</table>
[/if#pageaction]
Recent comments