[% 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='&lt;&lt; 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 &gt;&gt;'></td>
  </tr>
  <tr>
    <td colspan='9'>&nbsp;</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'>&nbsp;</td>
  </tr>
  <tr>
    <td align='left' colspan='2'><input type='submit' name='prev' value='&lt;&lt; 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 &gt;&gt;'></td>
  </tr>
</table><br><br>
</div>

[% END # Close WRAPPER %]