fannie/modules/plugins2.0/TimesheetPlugin/TsWagesReport.php
<?php
include(dirname(__FILE__).'/../../../config.php');
if (!class_exists('FannieAPI')) {
include_once(__DIR__ . '/../../../classlib2.0/FannieAPI.php');
}
class TsWagesReport extends FanniePage {
public $page_set = 'Plugin :: TimesheetPlugin';
function preprocess(){
$this->header = "Timeclock - Department Totals Report";
$this->title = "Timeclock - Department Totals Report";
setlocale(LC_MONETARY, 'en_US');
return True;
}
function body_content(){
global $FANNIE_OP_DB, $FANNIE_PLUGIN_SETTINGS;
$ts_db = FannieDB::get($FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']);
include('./includes/header.html');
// FULL TIME: Number of hours per week
$ft = 40;
echo "<form action='".$_SERVER['PHP_SELF']."' method=GET class=\"form-horizontal\">";
$currentQ = $ts_db->prepare("SELECT periodID
FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.payperiods
WHERE ".$ts_db->now()." BETWEEN periodStart AND periodEnd");
$currentR = $ts_db->execute($currentQ);
list($ID) = $ts_db->fetch_row($currentR);
$query = $ts_db->prepare("SELECT date_format(periodStart, '%M %D, %Y') as periodStart,
date_format(periodEnd, '%M %D, %Y') as periodEnd, periodID
FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.payperiods
WHERE periodStart < ".$ts_db->now()." ORDER BY periodID DESC");
$result = $ts_db->execute($query);
echo '<div class="row form-group">
<label class="col-sm-2">Starting Pay Period</label>
<div class="col-sm-5">
<select class="form-control" name="period">
<option>Please select a starting pay period.</option>';
while ($row = $ts_db->fetchRow($result)) {
echo "<option value=\"" . $row['periodID'] . "\"";
if ($row['periodID'] == $ID) { echo ' SELECTED';}
echo ">(" . $row['periodStart'] . " - " . $row['periodEnd'] . ")</option>";
}
echo "</select></div></div>";
echo '<div class="row form-group">
<label class="col-sm-2">Ending Pay Period</label>
<div class="col-sm-5">
<select class="form-control" name="end">
<option value=0>Please select an ending pay period.</option>';
$result = $ts_db->execute($query);
while ($row = $ts_db->fetchRow($result)) {
echo "<option value=\"" . $row['periodID'] . "\"";
if ($row['periodID'] == $ID) { echo ' SELECTED';}
echo ">(" . $row['periodStart'] . " - " . $row['periodEnd'] . ")</option>";
}
echo '</select></div></div>
<p>
<button class="btn btn-default" value="export" name="Export">Export</button>
</p>
</form>';
if (FormLib::get_form_value('Export') == 'export') {
$periodID = FormLib::get_form_value('period',0);
$end = FormLib::get_form_value('end',$periodID);
if ($end == 0) $end = $periodID;
// BEGIN TITLE
//
$query1 = $ts_db->prepare("SELECT date_format(periodStart, '%M %D, %Y') as periodStart, periodID
FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.payperiods WHERE periodID = ?");
$result1 = $ts_db->execute($query1,array($periodID));
$periodStart = $ts_db->fetch_row($result1);
$query2 = $ts_db->prepare("SELECT date_format(periodEnd, '%M %D, %Y') as periodEnd, periodID
FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.payperiods WHERE periodID = ?");
$result2 = $ts_db->execute($query2,array($end));
$periodEnd = $ts_db->fetch_row($result2);
// $periodct = ($end !== $periodID) ? $end - $periodID : 1;
$p = array();
$periodct = 0;
for ($i = $periodStart[1]; $i <= $periodEnd[1]; $i++) {
// echo $i;
$periodct++;
$p[] = $i;
}
echo "<br />";
echo "<h3>" . $periodStart[0] . " — " . $periodEnd[0] . "</h3>";
echo "Number of payperiods: " . $periodct;
//
// END TITLE
$query = $ts_db->prepare("SELECT s.ShiftID as id,
IF(s.NiceName='', s.ShiftName, s.NiceName) as area
FROM (SELECT ShiftID, NiceName, ShiftName, ShiftOrder
FROM ".$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase'].".shifts WHERE visible = 1 AND ShiftID <> 31) s
GROUP BY s.ShiftID ORDER BY s.ShiftOrder");
// echo $query;
$result = $ts_db->execute($query);
echo "<table class=\"table table-bordered table-striped\"><thead>\n<tr>
<th>ID</th><th>Area</th><th>Total Hrs</th><!--<th>agg</th>--><th>wages</th></tr></thead>\n<tbody>\n";
$queryP = $ts_db->prepare("SELECT SUM(t.hours) as total
FROM ". $FANNIE_PLUGIN_SETTINGS['TimesheetDatabase'].".timesheet t
WHERE t.periodID >= ? AND t.periodID <= ? AND t.area = ?");
$query2P = $ts_db->prepare("
SELECT SUM(e.wage) as agg
FROM ".$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase'].".TimesheetEmployees e, ".
$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase'].".timesheet t
WHERE t.emp_no = e.timesheetEmployeeID AND t.periodID >= ?
AND t.periodID <= ? AND t.area = ?");
$nfm = new NumberFormatter('en_US', NumberFormatter::CURRENCY);
while ($row = $ts_db->fetch_row($result)) {
echo "<tr><td>".$row['id']."</td><td>".$row['area']."</td><td align='right'>";
$result1 = $ts_db->execute($queryP,array($periodID,$end,$row['id']));
$totHrs = $ts_db->fetch_row($result1);
$tot = ($totHrs[0]) ? $totHrs[0] : 0;
echo $tot . "</td>";
$totArray[] = $tot;
// $totArray = array();
// array_push($totArray, $tot);
// foreach ($tot as $t) {
// $totArray[] = $t;
// }
$result2 = $ts_db->execute($query2P,array($periodID,$end,$row['id']));
$totAgg = $ts_db->fetch_row($result2);
$agg = ($totAgg[0]) ? $totAgg[0] : 0;
// echo "<td align='right'>$agg</td><td align='right'>";
$wages = $tot * $agg;
echo "<td align='right'>" . $nfm->format($wages) . "</td></tr>\n";
$wageArray[] = $wages;
// $wageArray = array();
// array_push($wageArray, $wages);
// foreach ($wages as $w) {
// $wageArray[] = $w;
// }
if ($row['id'] == "31") $csvwages .= ""; // Hide PTO from copy&paste output
else $csvwages .= $wages . "\t";
if ($row['id'] == "31") $csvhours .= "";
else $csvhours .= $tot . "\t";
}
// print_r($totArray);
echo "<tr><td colspan=4><hr /></td></tr>";
echo "<tr><td> </td><td><b>TOTALS</b></td>
<td align=right><b>" . number_format(array_sum($totArray),2) . "</b></td>
<td align=right><b>" . number_format(array_sum($wageArray),2) . "</b></td></tr>";
//
// OVERTIME
//
$OT1 = array();
$OT2 = array();
$employees = new TimesheetEmployeesModel($ts_db);
$employees->active(1);
$weekoneP = $ts_db->prepare("SELECT ROUND(SUM(hours), 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 <> 31
AND t.tdate >= DATE(p.periodStart)
AND t.tdate < DATE(date_add(p.periodStart, INTERVAL 7 day))");
$weektwoQ = $ts_db->prepare("SELECT ROUND(SUM(hours), 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 <> 31
AND t.tdate >= DATE(date_add(p.periodStart, INTERVAL 7 day))
AND t.tdate <= DATE(p.periodEnd)");
foreach ($p as $v) {
foreach ($employees->find() as $employee) {
$row = array('emp_no' => $employee->timesheetEmployeeID());
$weekoneR = $ts_db->execute($weekoneP,array($row['emp_no'],$v));
$weektwoR = $ts_db->execute($weektwoP,array($row['emp_no'],$v));
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 ($weekone > $ft) $otime1 = $weekone - $ft;
if ($weektwo > $ft) $otime2 = $weektwo - $ft;
// $otime = $otime + $otime1 + $otime2;
$OT1[] = $otime1;
$OT2[] = $otime2;
$otime1 = 0;
$otime2 = 0;
}
}
// print_r($OT1);
$OT = array_sum($OT1) + array_sum($OT2);
$OTTOT = number_format($OT,2);
echo "<tr><td> </td><td>OT Total</td><td align='right'>$OTTOT</td></tr>";
// END OVERTIME
// PTO REQUESTED
$ptoQ = $ts_db->prepare("SELECT SUM(t.hours) as total FROM ".
$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase'].".timesheet t
WHERE t.periodID >= ? AND t.periodID <= ? AND t.area = 31");
$ptoR = $ts_db->execute($ptoQ,array($periodID,$end));
$pto = $ts_db->fetch_row($ptoR);
$PTOREQ = number_format($pto[0],2);
echo "<tr><td> </td><td>PTO Requested</td><td align='right'>$PTOREQ</td></tr>";
// END PTO REQUESTED
// PTO NEW
$nonPTOtotalP = $ts_db->prepare("SELECT SUM(hours)
FROM ".$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase'].".timesheet
WHERE periodID >= ? AND periodID <= ? AND area <> 31
AND emp_no = ?");
$PTOnew = array();
foreach ($employees->find() as $employee) {
$nonPTOtotalr = $ts_db->execute($nonPTOtotalP,array($periodID,$end,$employee->timesheetEmployeeID()));
$nonPTOtotal = $ts_db->fetch_row($nonPTOtotalr);
$ptoAcc = $nonPTOtotal[0] * 0.075;
$PTOnew[] = $ptoAcc;
}
// print_r($PTOnew);
$PTONEW = number_format(array_sum($PTOnew),2);
echo "<tr><td> </td><td>PTO New</td><td align='right'>$PTONEW</td></tr>";
// END PTO NEW
echo "</tbody></table>\n";
echo "<br />";
echo "<a id='copyLink2'>Copy</a> & Paste <b>Hours</b> data (columns C:AB):";
echo "<table border=0><tr><td><textarea id='copyMe2' cols=50 rows=3>" . $csvhours . "</textarea></td></tr></table>";
echo "<a id='copyLink3'>Copy</a> & Paste the OT/PTO <b>Hours</b> data (columns AG:AI):";
echo "<table border=0><tr><td><textarea id='copyMe3' cols=50 rows=1>$OTTOT\t$PTOREQ\t$PTONEW</textarea></td></tr></table>";
// echo "<br />";
echo "<a id='copyLink'>Copy</a> & Paste <b>Wages</b> data:";
echo "<table border=0><tr><td><textarea id='copyMe' cols=50 rows=5>" . $csvwages . "</textarea></td></tr></table>";
}
}
function javascript_content(){
ob_start();
?>
ZeroClipboard.setMoviePath( '../src/ZeroClipboard10.swf' );
var clip = new ZeroClipboard.Client();
clip.setText( '' ); // will be set later on mouseDown
clip.setHandCursor( true );
clip.setCSSEffects( true );
clip.addEventListener( 'load', function(client) {
// alert( "movie is loaded" );
});
clip.addEventListener( 'complete', function(client, text) {
alert("Copied text to clipboard: " + text );
});
clip.addEventListener( 'mouseOver', function(client) {
// alert("mouse over");
});
clip.addEventListener( 'mouseOut', function(client) {
// alert("mouse out");
});
clip.addEventListener( 'mouseDown', function(client) {
// set text to copy here
clip.setText( document.getElementById('copyMe').value );
// alert("mouse down");
});
clip.addEventListener( 'mouseUp', function(client) {
// alert("mouse up");
});
clip.glue( 'copyLink' );
var clip1 = new ZeroClipboard.Client();
clip1.setText( '' ); // will be set later on mouseDown
clip1.setHandCursor( true );
clip1.setCSSEffects( true );
clip1.addEventListener( 'load', function(client) {
// alert( "movie is loaded" );
});
clip1.addEventListener( 'complete', function(client, text) {
alert("Copied text to clip1board: " + text );
});
clip1.addEventListener( 'mouseOver', function(client) {
// alert("mouse over");
});
clip1.addEventListener( 'mouseOut', function(client) {
// alert("mouse out");
});
clip1.addEventListener( 'mouseDown', function(client) {
// set text to copy here
clip1.setText( document.getElementById('copyMe2').value );
// alert("mouse down");
});
clip1.addEventListener( 'mouseUp', function(client) {
// alert("mouse up");
});
clip1.glue( 'copyLink2' );
var clip2 = new ZeroClipboard.Client();
clip2.setText( '' ); // will be set later on mouseDown
clip2.setHandCursor( true );
clip2.setCSSEffects( true );
clip2.addEventListener( 'load', function(client) {
// alert( "movie is loaded" );
});
clip2.addEventListener( 'complete', function(client, text) {
alert("Copied text to clip2board: " + text );
});
clip2.addEventListener( 'mouseOver', function(client) {
// alert("mouse over");
});
clip2.addEventListener( 'mouseOut', function(client) {
// alert("mouse out");
});
clip2.addEventListener( 'mouseDown', function(client) {
// set text to copy here
clip2.setText( document.getElementById('copyMe3').value );
// alert("mouse down");
});
clip2.addEventListener( 'mouseUp', function(client) {
// alert("mouse up");
});
clip2.glue( 'copyLink3' );
<?php
return ob_get_clean();
}
}
FannieDispatch::conditionalExec(false);