rendered paste body[% PROCESS "_dbiconnect.htm" %]
[% PROCESS "_security.htm" %]
[% PROCESS "_instructorsonly.htm" %]
[% # Set the wrapper to use
IF params.print == 1;
mywrapper = "report_print.pdf";
myformat = "_standard_wrapper_user_list_print.htm";
ELSE;
mywrapper = "_standard_wrapper_outer.htm";
myformat = "_standard_wrapper_user_list.htm";
END %]
[% WRAPPER $mywrapper %]
[% title="Staff Rota";
thispage = template.name | remove(".htm");
return.page= "reports.htm";
page_params = "?start_hidden=" _ params.start_hidden
_ "&finish_hidden=" _ params.finish_hidden
_ "&member_type=" _ params.member_type;
link1.page = "rota.htm";
link1.params = page_params;
link1.text = "ROTA";
%]
<style type="text/css">
.emphasis {
font-style: italic;
}
.instr_hover {
background-color: #e2ecfa;
border-right: 2px solid #b6d0f1;
}
.high {
color: #800000;
font-weight: bold;
}
.low {
color: #008000;
font-weight: bold;
}
.high_block {
background-color: #800000;
float: left;
height: 20px;
}
.low_block {
background-color: #008000;
float: left;
height: 20px;
}
.header_row {
border-bottom-width: 4px;
}
.total_row {
border-top-width: 4px;
}
TH.instr_name {
width: 300px;
height: 30px;
}
TD.interactions {
text-align: left;
}
TD.totals {
width: 75px;
}
</style>
<script language="javascript" type="text/javascript">
<!--
function validateForm(frm) {
for (var i=0; i<frm.elements.length; i++) {
var ele = frm.elements[i];
if (ele.type == 'text') {
if (ele.value != "" && isNaN(ele.value)) {
alert("Please enter only numbers");
ele.focus();
return false;
}
}
}
return true;
}
String.prototype.explode = function(sep, num) {
var p = this.split(sep);
if (!num) return p;
var ret = [];
for (var x=0; p.length; x++) {
if (x==num-1) {
ret[x] = p.join(sep);
return ret;
}
ret[x] = p.shift();
}
return ret;
}
Number.prototype.round = function(dp) {
if (dp == 0)
return Math.round(this);
var temp = this*(10*dp);
temp = Math.round(temp);
return temp/(10*dp);
}
function updateValue(evnt) {
var oSource = evnt.target || event.srcElement;
var oForm = oSource.form;
var pSource = oSource.name.explode("_", 3);
var oStdWork = document.getElementsByName(pSource[0]+"_"+pSource[1]+"_standard_working_hours")[0];
var oStdInt = document.getElementsByName(pSource[0]+"_"+pSource[1]+"_standard_interaction_hours")[0];
var oWork = document.getElementsByName(pSource[0]+"_"+pSource[1]+"_working_hours")[0];
var oInt = document.getElementsByName(pSource[0]+"_"+pSource[1]+"_interaction_hours")[0];
var oTarget = document.getElementsByName(pSource[0]+"_"+pSource[1]+"_target_interactions")[0];
var oIntPerWeek = document.getElementsByName(pSource[0]+"_"+pSource[1]+"_interactions_per_week")[0];
switch (pSource[2]) {
case 'standard_working_hours':
if (oStdWork.getAttribute("lastValue") == oWork.value) {
oWork.value = oStdWork.value;
}
break;
case 'standard_interaction_hours':
if (oStdInt.getAttribute("lastValue") == oInt.value) {
oInt.value = oStdInt.value;
}
break;
}
oSource.setAttribute("lastValue", oSource.value);
calcIntPerHour(oInt, oTarget, oIntPerWeek);
}
function calcIntPerHour(oInt, oTarget, oIntPerWeek) {
oIntPerWeek.value = (oTarget.value*oInt.value).round(0);
}
function displayLeaving(oButton, which) {
var oLeaving = document.getElementsByName(which+"_leaving_day")[0];
oLeaving.style.display = '';
if (oLeaving.selectedIndex != 0) {
oButton.form.submit();
}
}
// -->
</script>
<div class="main">
<div class="tabletitle">
<h3>Staff Rota</h3>
</div>
<div class="spacer"></div>
<!-- <div class="tablefloatcontents">
<p>
<input type="button" value="PRINT" class="update" style="width:130px; background-color: #DDBC99" onclick="winopenprint('report_interact_staff.pdf', 'start_date=[% start_date %]&end_date=[% end_date %]&member_type=[% params.member_type %]&int_type=[% params.int_type %]')">
Print report
</p>
</div>
//-->
<!-- <div class="tablefloatcontents">
<p>
<input type="button" value="EXPORT" class="update" style="width:130px; background-color: #DDBC99" onclick="window.location.href='report_interact_staff.xls?site_id=[% site_id %]&start_date=[% start_date %]&end_date=[% end_date %]&member_type=[% params.member_type %]&int_type=[% params.int_type %]'">
Export complete report to Microsoft Excel.
<span class="emphasis">Please note, these may take a short while to display</span>
</p>
</div>
//-->
[% PERL; %]
my $dbi = $stash->get("DBI"); #we'll need these later
my $dbh = $dbi->{_DBH};
my $CGI = $stash->get("CGI");
my $curdate = $CGI->param('curdate');
my $thisMonday;
my $site_id = $stash->get("site_id");
if ($curdate) { #if curdate, we have a posted date, we need to check to see if anything needs updating
#load the last values from the DB so we know if they cascade from previous, or really need updating
#working/interaction hours first
my $qLastValues = $dbh->prepare("
SELECT
rs.user_id,
LEFT(
GROUP_CONCAT(rs.working_hours ORDER BY rs.date DESC),
LOCATE(',', CONCAT(GROUP_CONCAT(rs.working_hours ORDER BY rs.date DESC), ',')) -1
) as working_hours,
LEFT(
GROUP_CONCAT(rs.interaction_hours ORDER BY rs.date DESC),
LOCATE(',', CONCAT(GROUP_CONCAT(rs.interaction_hours ORDER BY rs.date DESC), ',')) -1
) as interaction_hours
FROM rota_standard rs
WHERE
rs.site_id = ? AND rs.date < '$curdate'
GROUP BY rs.user_id
ORDER BY rs.user_id ASC
"); #">
$qLastValues->execute($site_id);
my %standard_working;
my %standard_interaction;
while (my $r = $qLastValues->fetchrow_hashref()) {
$standard_working{$r->{user_id}} = $r->{working_hours};
$standard_interaction{$r->{user_id}} = $r->{interaction_hours};
}
#target next
my $qLastTarget = $dbh->prepare("
SELECT
rl.user_id,
LEFT(
GROUP_CONCAT(rl.target_interactions ORDER BY rl.date DESC),
LOCATE(',', CONCAT(GROUP_CONCAT(rl.target_interactions ORDER BY rl.date DESC), ',')) -1
) as last_target_interactions
FROM rota rl
WHERE
rl.site_id = ? AND
rl.date < ?
GROUP BY rl.site_id, rl.user_id
"); #">
$qLastTarget->execute($site_id, $curdate);
my %target;
while (my $r = $qLastTarget->fetchrow_hashref()) {
$target{$r->{user_id}} = $r->{last_target_interactions};
}
#put all the posted values in a nicely structured array (if only perl handled forms like php)
my $values;
for (keys %{$CGI->params}) {
if (/${site_id}_(\d+)_(\S+)/) {
$values->{$1}{$2} = $CGI->param($_);
$values->{$1}{$2} = undef
if ($values->{$1}{$2} eq ''); #if nothing was entered, it's null, not ''
}
}
#prepare the queries we will be using to keep the database up to date
my $qData = $dbh->prepare("
INSERT INTO rota (site_id, user_id, date, working_hours, interaction_hours, target_interactions)
VALUES (?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY
UPDATE working_hours = VALUES(working_hours), interaction_hours = VALUES(interaction_hours), target_interactions = VALUES(target_interactions)
");
my $qDataDel = $dbh->prepare("
DELETE FROM rota
WHERE site_id = ? AND user_id = ? AND date = ?
");
my $qStandard = $dbh->prepare("
INSERT INTO rota_standard (site_id, user_id, date, working_hours, interaction_hours)
VALUES (?, ?, ?, ?, ?)
ON DUPLICATE KEY
UPDATE working_hours = VALUES(working_hours), interaction_hours = VALUES(interaction_hours)
");
my $qStandardDel = $dbh->prepare("
DELETE FROM rota_standard
WHERE site_id = ? AND user_id = ? AND date = ?
");
for my $uid (keys %{$values}) { #loop through previously created datastructure by user id
#if our new value is the same as our old value, mark it as null
$values->{$uid}{'working_hours'} = undef
if ($values->{$uid}{'standard_working_hours'} == $values->{$uid}{'working_hours'});
$values->{$uid}{'interaction_hours'} = undef
if ($values->{$uid}{'standard_interaction_hours'} == $values->{$uid}{'interaction_hours'});
$values->{$uid}{'target_interactions'} = undef
if ($values->{$uid}{'target_interactions'} == $target{$uid});
$values->{$uid}{'standard_working_hours'} = undef
if ($values->{$uid}{'standard_working_hours'} == $standard_working{$uid});
$values->{$uid}{'standard_interaction_hours'} = undef
if ($values->{$uid}{'standard_interaction_hours'} == $standard_interaction{$uid});
#if anything is defined (not null), then we need an update query, otherwise delete
if (defined $values->{$uid}{'working_hours'} || defined $values->{$uid}{'interaction_hours'} || defined $values->{$uid}{'target_interactions'}) {
$qData->execute($site_id, $uid, $curdate, $values->{$uid}{'working_hours'}, $values->{$uid}{'interaction_hours'}, $values->{$uid}{'target_interactions'});
} else {
$qDataDel->execute($site_id, $uid, $curdate);
}
if (defined $values->{$uid}{'standard_working_hours'} || $values->{$uid}{'standard_interaction_hours'}) {
$qStandard->execute($site_id, $uid, $curdate, $values->{$uid}{'standard_working_hours'}, $values->{$uid}{'standard_interaction_hours'});
} else {
$qStandardDel->execute($site_id, $uid, $curdate);
}
}
#if we posted something with the name "prev" we want to move back a week, "next" next week, otherwise, stay the same
if ($CGI->param("prev")) {
my $qmonday = $dbh->prepare("SELECT DATE_FORMAT(DATE_SUB('".$curdate."', INTERVAL 7 DAY), '%Y-%m-%d') as last_monday");
$qmonday->execute();
$thisMonday = ($qmonday->fetchrow_array())[0];
} elsif ($CGI->param("next")) {
my $qmonday = $dbh->prepare("SELECT DATE_FORMAT(DATE_ADD('".$curdate."', INTERVAL 7 DAY), '%Y-%m-%d') as last_monday");
$qmonday->execute();
$thisMonday = ($qmonday->fetchrow_array())[0];
} else {
$thisMonday = $curdate;
}
} else {
#it's our first time, find last Monday
my $wday = (localtime)[6]; #current weekday, 0 = sunday, 6 = saturday
$wday = 7 if ($wday == 0); #we want our weeks to start on monday
--$wday;
my $qmonday = $dbh->prepare("SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL $wday DAY), '%Y-%m-%d') as last_monday");
$qmonday->execute();
$thisMonday = ($qmonday->fetchrow_array())[0];
}
#now we've got $thisMonday and we've updated all we need to, get the last "standard" data for cascade
my $qLastValues = $dbh->prepare("
SELECT
rs.user_id,
LEFT(
GROUP_CONCAT(rs.working_hours ORDER BY rs.date DESC),
LOCATE(',', CONCAT(GROUP_CONCAT(rs.working_hours ORDER BY rs.date DESC), ',')) -1
) as working_hours,
LEFT(
GROUP_CONCAT(rs.interaction_hours ORDER BY rs.date DESC),
LOCATE(',', CONCAT(GROUP_CONCAT(rs.interaction_hours ORDER BY rs.date DESC), ',')) -1
) as interaction_hours
FROM rota_standard rs
WHERE
rs.site_id = ? AND rs.date <= '$thisMonday'
GROUP BY rs.user_id
ORDER BY rs.user_id ASC
"); #"> and again
$qLastValues->execute($site_id);
my %standard_working;
my %standard_interaction;
while (my $r = $qLastValues->fetchrow_hashref()) {
$standard_working{$r->{user_id}} = $r->{working_hours};
$standard_interaction{$r->{user_id}} = $r->{interaction_hours};
}
#get the latest non-standard data
my $qfetch = $dbh->prepare("
SELECT
u.site_id, u.user_id, CONCAT(ud.fname, ' ', ud.sname) as name, ut.name as type,
r.working_hours, r.interaction_hours, r.target_interactions,
r.date,
LEFT(
GROUP_CONCAT(rl.target_interactions ORDER BY rl.date DESC),
LOCATE(',', CONCAT(GROUP_CONCAT(rl.target_interactions ORDER BY rl.date DESC), ',')) -1
) as last_target_interactions
FROM users u
LEFT JOIN user_details ud ON
u.site_id = ud.site_id AND
u.user_id = ud.user_id
LEFT JOIN user_types ut ON
ut.type_id = u.type_id
LEFT JOIN rota r ON
r.site_id = u.site_id AND
r.user_id = u.user_id AND
r.date = ?
LEFT JOIN rota rl ON
rl.site_id = u.site_id AND
rl.user_id = u.user_id AND
rl.date <= ?
WHERE
u.type_id IN(2,3) AND
u.site_id = ? AND
u.user_id NOT IN (10002, 10042)
GROUP BY u.site_id, u.user_id
ORDER BY ud.sname, ud.fname
"); #">
$qfetch->execute($thisMonday, $thisMonday, $site_id);
print "
<form method='post' action='rota.htm' onSubmit='return validateForm(this);'>
<div style='display: none;'><input type='submit' value='Update'></div>
<input type='hidden' name='curdate' value='$thisMonday'>
<h2 style='text-align: center;'>Rota for week commencing $thisMonday</h2><br>
<table cellpadding='2' cellspacing='0' align='center'>
<tr>
<td align='left' colspan='2'><input type='submit' name='prev' value='<< Previous Week'></td>
<td align='center' colspan='5'><input type='submit' value='Update'></td>
<td align='right' colspan='2'><input type='submit' name='next' value='Next Week >>'></td>
</tr>
<tr>
<td colspan='9'> </td>
</tr>
<tr>
<th colspan='2'></th>
<th colspan='2'>Standard week</th>
<th colspan='4'>This week</th>
</tr>
<tr valign='top'>
<th>Name</th>
<th>Type</th>
<th>Working<br>hours</th>
<th>Interaction<br>hours</th>
<th>Working<br>hours</th>
<th>Interaction<br>hours</th>
<th>Interactions<br>per hour</th>
<th>Interactions<br>per week</th>
<th width='185'>Remove instructor</th>
</tr>
";
my $first = 1;
while (my $r = $qfetch->fetchrow_hashref()) {
$r->{working_hours} = $standard_working{$r->{user_id}}
if (!defined $r->{working_hours});
$r->{interaction_hours} = $standard_interaction{$r->{user_id}}
if (!defined $r->{interaction_hours});
$r->{target_interactions} = $r->{last_target_interactions}
if (!defined $r->{target_interactions});
print "
<tr>
<td>$r->{name}</td>
<td>$r->{type}</td>
";
my $sprintf = '<td align="center"><input type="text" size="6" name="%1$s" value="%2$s" lastValue="%2$s" onBlur="updateValue(event);"%3$s></td>'."\n";
print sprintf($sprintf, $r->{site_id}.'_'.$r->{user_id}.'_standard_working_hours', $standard_working{$r->{user_id}});
print sprintf($sprintf, $r->{site_id}.'_'.$r->{user_id}.'_standard_interaction_hours', $standard_interaction{$r->{user_id}});
print sprintf($sprintf, $r->{site_id}.'_'.$r->{user_id}.'_working_hours', $r->{working_hours});
print sprintf($sprintf, $r->{site_id}.'_'.$r->{user_id}.'_interaction_hours', $r->{interaction_hours});
print sprintf($sprintf, $r->{site_id}.'_'.$r->{user_id}.'_target_interactions', $r->{target_interactions});
print sprintf($sprintf, $r->{site_id}.'_'.$r->{user_id}.'_interactions_per_week', ($r->{interaction_hours} == 0 ? '' : sprintf('%d', $r->{target_interactions}*$r->{interaction_hours})), ' readonly');
print "<td><select name='$r->{site_id}_$r->{user_id}_leaving_day' style='display: none;'><option value=''>Leaving Day<option value='0'>Monday<option value='1'>Tuesday<option value='2'>Wednesday<option value='3'>Thursday<option value='4'>Friday<option value='5'>Saturday<option value='6'>Sunday</select> <input type='button' value='Remove' onClick='displayLeaving(this, \"$r->{site_id}_$r->{user_id}\");'></td>";
print "
</tr>
";
}
[% END; %]
<tr>
<td colspan='9'> </td>
</tr>
<tr>
<td align='left' colspan='2'><input type='submit' name='prev' value='<< Previous Week'></td>
<td align='center' colspan='5'><input type='submit' value='Update'></td>
<td align='right' colspan='2'><input type='submit' name='next' value='Next Week >>'></td>
</tr>
</table><br><br>
</div>
[% END # Close WRAPPER %]