fannie/modules/plugins2.0/TimesheetPlugin/PayrollDetail.php
<?php
include(dirname(__FILE__).'/../../../config.php');
if (!class_exists('FannieAPI')) {
include_once(__DIR__ . '/../../../classlib2.0/FannieAPI.php');
}
class PayrollDetail extends FanniePage {
public $page_set = 'Plugin :: TimesheetPlugin';
protected $title = 'Payroll Detail';
protected $header = 'Payroll Detail';
function preprocess(){
$this->addCssFile('includes/style.css');
return True;
}
function body_content(){
global $FANNIE_PLUGIN_SETTINGS, $FANNIE_OP_DB;
$ts_db = FannieDB::get($FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']);
$periodID = FormLib::get_form_value('periodID',False);
$emp_no = FormLib::get_form_value('emp_no',False);
if (is_numeric($periodID) && is_numeric($emp_no)) { // If submitted.
$query = $ts_db->prepare("SELECT ROUND(SUM(TIMESTAMPDIFF(MINUTE, t.time_in, t.time_out))/60, 2),
date_format(t.date, '%a %b %D'),
t.emp_no,
e.FirstName,
date_format(p.periodStart, '%M %D, %Y'),
date_format(p.periodEnd, '%M %D, %Y'),
t.date
FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.timesheet AS t
INNER JOIN {$FANNIE_OP_DB}.employees AS e
ON (t.emp_no = e.emp_no)
INNER JOIN {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.payperiods AS p
ON (t.periodID = p.periodID)
WHERE t.emp_no = ?
AND t.periodID = ?
AND t.area <> 13
GROUP BY t.date");
$weekoneQ = $ts_db->prepare("SELECT ROUND(SUM(TIMESTAMPDIFF(MINUTE, t.time_in, t.time_out))/60, 2)
FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.timesheet AS t
INNER JOIN {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.payperiods AS p
ON (p.periodID = t.periodID)
WHERE t.emp_no = ?
AND t.periodID = ?
AND t.area <> 13
AND t.date >= DATE(p.periodStart)
AND t.date < DATE(date_add(p.periodStart, INTERVAL 7 day))");
$weektwoQ = $ts_db->prepare("SELECT ROUND(SUM(TIMESTAMPDIFF(MINUTE, t.time_in, t.time_out))/60, 2)
FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.timesheet AS t
INNER JOIN {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.payperiods AS p
ON (p.periodID = t.periodID)
WHERE t.emp_no = ?
AND t.periodID = ?
AND t.area <> 13
AND t.date >= DATE(date_add(p.periodStart, INTERVAL 7 day)) AND t.date <= DATE(p.periodEnd)");
$vacationQ = $ts_db->prepare("SELECT ROUND(vacation, 2)
FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.timesheet AS t
WHERE t.emp_no = ?
AND t.periodID = ?
AND t.area = 13");
$weekoneR = $ts_db->execute($weekoneQ,array($emp_no,$periodID));
$weektwoR = $ts_db->execute($weektwoQ,array($emp_no,$periodID));
$vacationR = $ts_db->execute($vacationQ,array($emp_no,$periodID));
list($weekone) = $ts_db->fetch_row($weekoneR);
if (is_null($weekone)) $weekone = 0;
list($weektwo) = $ts_db->fetch_row($weektwoR);
if (is_null($weektwo)) $weektwo = 0;
if ($ts_db->num_rows($vacationR) != 0) {
list($vacation) = $ts_db->fetch_row($vacationR);
} elseif (is_null($vacation)) {
$vacation = 0;
} else {
$vacation = 0;
}
$result = $ts_db->execute($query,array($emp_no,$periodID));
if ($ts_db->num_rows($result) > 0) {
ob_start();
$first = TRUE;
$periodHours = 0;
while ($row = $ts_db->fetch_row($result)) {
if ($first == TRUE) {
echo "<p>Timesheet for $row[3] from $row[4] to $row[5]:</p>";
echo '<table><tr><th>Date</th><th>Total Hours Worked</th><th></th></tr>';
}
if ($row[0] > 24) {$fontopen = '<font color="red">'; $fontclose = '</font>';}
else {$fontopen = NULL; $fontclose = NULL;}
echo "<tr><td>$row[1]</td><td>$fontopen$row[0]$fontclose</td><td><a target=\"_blank\" href=\"EditTimesheetDatePage.php?emp_no=$emp_no&periodID=$periodID&date=$row[6]\">(Edit)</a></td></tr>";
$first = FALSE;
$periodHours += $row[0];
}
$roundhour = explode('.', number_format($periodHours, 2));
if ($roundhour[1] < 13) {$roundhour[1] = 00;}
elseif ($roundhour[1] >= 13 && $roundhour[1] < 37) {$roundhour[1] = 25;}
elseif ($roundhour[1] >= 37 && $roundhour[1] < 63) {$roundhour[1] = 50;}
elseif ($roundhour[1] >= 63 && $roundhour[1] < 87) {$roundhour[1] = 75;}
elseif ($roundhour[1] >= 87) {$roundhour[1] = 00; $roundhour[0]++;}
$periodHours = number_format($roundhour[0] . '.' . $roundhour[1], 2);
echo "</table>
<p>Total hours in this pay period: $periodHours</p>
<p>Week One: ";
if ($weekone > 40) {echo '<font color="red">'; $font = '</font>';} else {$font = NULL;}
echo "$weekone" . $font . "</p>
<p>Week Two: ";
if ($weektwo > 40) {echo '<font color="red">'; $font = '</font>';} else {$font = NULL;}
echo "$weektwo" . $font . "</p>
<p>Vacation Pay: ";
if ($vacation > 0) {echo '<font color="red">'; $font = '</font>';} else {$font = NULL;}
echo "$vacation" . $font . "</p>";
return ob_get_clean();
}
else {
return '<p>There is no timesheet available for that pay period.</p>';
}
}
return 'Error: no data provided';
}
}
FannieDispatch::conditionalExec(false);