function body_content() { global $FANNIE_OP_DB, $FANNIE_PLUGIN_SETTINGS, $FANNIE_URL; $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">'; $stored = $_COOKIE['timesheet'] ? $_COOKIE['timesheet'] : ''; echo '<div class="row form-group">'; if ($_SESSION['logged_in'] == True) { echo '<label class="col-sm-2">Name</label> <div class="col-sm-5"><select name="emp_no" class="form-control"> <option value="error">Select staff member</option>' . "\n"; $model = new TimesheetEmployeesModel($ts_db); $model->active(1); foreach ($model->find('firstName') as $obj) { printf('<option value="%d">%s %s</option>', $obj->timesheetEmployeeID(), $obj->firstName(), substr($obj->lastName(), 0, 1)); } echo '</select></div>'; } else { echo "<label class=\"col-sm-2\">Employee Number</label>\n <div class=\"col-sm-5\">\n <input type='text' class=\"form-control\" name='emp_no' value='{$stored}' \n size=4 autocomplete='off' />\n </div>"; } echo '</div>'; $currentQ = $ts_db->prepare_statement("SELECT periodID \n FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.payperiods \n WHERE " . $ts_db->now() . " BETWEEN periodStart AND periodEnd"); $currentR = $ts_db->exec_statement($currentQ); list($ID) = $ts_db->fetch_row($currentR); $query = $ts_db->prepare_statement("SELECT date_format(periodStart, '%M %D, %Y') as periodStart, \n date_format(periodEnd, '%M %D, %Y') as periodEnd, periodID \n FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.payperiods \n WHERE periodStart < " . $ts_db->now() . " ORDER BY periodID DESC"); $result = $ts_db->exec_statement($query); echo '<div class="row form-group"> <label class="col-sm-2">Starting Pay Period</label> <div class="col-sm-5"><select name="period" class="form-control"> <option>Please select a starting pay period.</option>'; while ($row = $ts_db->fetch_array($result)) { echo "<option value=\"" . $row['periodID'] . "\""; if ($row['periodID'] == $ID) { echo ' SELECTED'; } echo ">(" . $row['periodStart'] . " - " . $row['periodEnd'] . ")</option>"; } echo "</select></div>"; echo '</div>'; echo '<div class="row form-group"> <label class="col-sm-2">Ending Pay Period</label> <div class="col-sm-5"> <select name="end" class="form-control"> <option value=0>Please select an ending pay period.</option>'; $result = $ts_db->exec_statement($query); while ($row = $ts_db->fetch_array($result)) { echo "<option value=\"" . $row['periodID'] . "\""; if ($row['periodID'] == $ID) { echo ' SELECTED'; } echo ">(" . $row['periodStart'] . " - " . $row['periodEnd'] . ")</option>"; } echo '</select></div> </div> <p> <button value="run" class="btn btn-default" name="run">Run</button> </p> </form>'; if (FormLib::get_form_value('run', '') == 'run') { $emp_no = FormLib::get_form_value('emp_no', 0); $employee = new TimesheetEmployeesModel($ts_db); $employee->timesheetEmployeeID($emp_no); if (!$employee->load()) { echo "<div id='alert'><h1>Error!</h1><p>Incorrect, invalid, or inactive employee number entered.</p>\n <p><a href='" . $_SERVER['PHP_SELF'] . "'>Please try again</a></p></div>"; } else { setcookie("timesheet", $emp_no, time() + 60 * 3); $periodID = FormLib::get_form_value('period', 0); $end = FormLib::get_form_value('end', $periodID); if ($end == 0) { $end = $periodID; } $query1 = $ts_db->prepare_statement("SELECT date_format(periodStart, '%M %D, %Y') as periodStart, \n periodID as pid \n FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.payperiods \n WHERE periodID = ?"); $result1 = $ts_db->exec_statement($query1, array($periodID)); $periodStart = $ts_db->fetch_row($result1); $query2 = $ts_db->prepare_statement("SELECT date_format(periodEnd, '%M %D, %Y') as periodEnd, \n periodID as pid \n FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.payperiods WHERE periodID = ?"); $result2 = $ts_db->exec_statement($query2, array($end)); $periodEnd = $ts_db->fetch_row($result2); $p = array(); for ($i = $periodStart[1]; $i < $periodEnd[1]; $i++) { $p[] = $i; } $firstppP = $ts_db->prepare_statement("SELECT MIN(periodID) \n FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.payperiods \n WHERE YEAR(periodStart) = YEAR(" . $ts_db->now() . ")"); $firstppR = $ts_db->exec_statement($firstppP); $firstpp = $ts_db->fetch_row($firstppR); $y = array(); for ($i = $firstpp[0]; $i <= $periodEnd[1]; $i++) { $y[] = $i; } echo "<h2>{$emp_no} — " . $employee->firstName() . " " . $employee->lastName() . "</h2>"; // BEGIN TITLE // $query1 = $ts_db->prepare_statement("SELECT date_format(periodStart, '%M %D, %Y') as periodStart, \n periodID as pid, DATE(periodStart) \n FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.payperiods \n WHERE periodID = ?"); $result1 = $ts_db->exec_statement($query1, array($periodID)); $periodStart = $ts_db->fetch_row($result1); $query2 = $ts_db->prepare_statement("SELECT date_format(periodEnd, '%M %D, %Y') as periodEnd, \n periodID as pid, DATE(periodEnd) \n FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.payperiods \n WHERE periodID = ?"); $result2 = $ts_db->exec_statement($query2, array($end)); $periodEnd = $ts_db->fetch_row($result2); // $periodct = ($end !== $periodID) ? $end - $periodID : 1; for ($i = $periodStart[1]; $i <= $periodEnd[1]; $i++) { // echo $i; $periodct++; $p[] = $i; } echo "<h3>" . $periodStart[0] . " — " . $periodEnd[0] . "</h3>\n"; echo "Number of payperiods: " . $periodct . "\n"; // // END TITLE echo "<br />"; $areasq = $ts_db->prepare_statement("SELECT ShiftName, ShiftID \n FROM " . $FANNIE_PLUGIN_SETTINGS['TimesheetDatabase'] . ".shifts \n WHERE visible = 1 ORDER BY ShiftOrder"); $areasr = $ts_db->exec_statement($areasq); $shiftInfo = array(); echo "<table class=\"table table-bordered table-striped\"><thead>\n<tr><th>Week</th><th>Name</th><th>Wage</th>"; while ($areas = $ts_db->fetch_array($areasr)) { echo "<div id='vth'><th>" . substr($areas[0], 0, 6) . "</th></div>"; // -- TODO vertical align th, static col width $shiftInfo[$areas['ShiftID']] = $areas['ShiftName']; } echo "</th><th>PTO new</th><th>Total</th><th>OT</th></tr></thead>\n<tbody>\n"; $weekQ = $ts_db->prepare_statement("SELECT emp_no, area, tdate, periodID, \n hours, WEEK(tdate) as week_number \n FROM " . $FANNIE_PLUGIN_SETTINGS['TimesheetDatabase'] . ".timesheet \n WHERE emp_no = ?\n AND tdate >= ? AND tdate <= ?\n GROUP BY WEEK(tdate)"); $weekR = $ts_db->exec_statement($weekQ, array($emp_no, $periodStart[2], $periodEnd[2])); $totalP = $ts_db->prepare_statement("SELECT SUM(hours) FROM " . $FANNIE_PLUGIN_SETTINGS['TimesheetDatabase'] . ".timesheet \n WHERE periodID >= ? AND periodID <= ? AND emp_no = ?"); $depttotP = $ts_db->prepare_statement("SELECT SUM(t.hours) \n FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.timesheet t \n WHERE WEEK(t.tdate) = ? AND t.emp_no = ? AND t.area = ?"); $nonPTOtotalP = $ts_db->prepare_statement("SELECT SUM(hours) FROM " . $FANNIE_PLUGIN_SETTINGS['TimesheetDatabase'] . ".timesheet \n WHERE periodID >= ? AND periodID <= ? AND area <> 31 \n AND emp_no = ?"); $weekoneP = $ts_db->prepare_statement("SELECT ROUND(SUM(hours), 2) \n FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.timesheet AS t\n INNER JOIN {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.payperiods \n AS p ON (p.periodID = t.periodID)\n WHERE t.emp_no = ?\n AND t.periodID = ?\n AND t.area <> 31\n AND t.tdate >= DATE(p.periodStart)\n AND t.tdate < DATE(date_add(p.periodStart, INTERVAL 7 day))"); $weektwoP = $ts_db->prepare_statement("SELECT ROUND(SUM(hours), 2)\n FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.timesheet AS t\n INNER JOIN {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.payperiods AS p\n ON (p.periodID = t.periodID)\n WHERE t.emp_no = ?\n AND t.periodID = ?\n AND t.area <> 31\n AND t.tdate >= DATE(date_add(p.periodStart, INTERVAL 7 day)) \n AND t.tdate <= DATE(p.periodEnd)"); while ($row = $ts_db->fetch_row($weekR)) { $week_no = $row['week_number']; $emp_no = $row['emp_no']; $totalr = $ts_db->exec_statement($totalP, array($periodID, $end, $emp_no)); $total = $ts_db->fetch_row($totalr); $color = $total[0] > 80 * $periodct ? "FF0000" : "000000"; echo "<tr><td>{$week_no}</td>"; echo "<td>" . ucwords($employee->firstName()) . " - " . ucwords(substr($employee->firstName(), 0, 1)) . ucwords(substr($employee->lastName(), 0, 1)) . "</td><td align='right'>\$" . $employee->wage() . "</td>"; $total0 = !$total[0] ? 0 : number_format($total[0], 2); // // LABOR DEPARTMENT TOTALS foreach ($shiftInfo as $area => $shiftName) { // echo $depttotq; $depttotr = $ts_db->exec_statement($depttotP, array($week_no, $emp_no, $area)); $depttot = $ts_db->fetch_row($depttotr); $depttotal = !$depttot[0] ? 0 : number_format($depttot[0], 2); echo "<td align='right'>" . $depttotal . "</td>"; } // END LABOR DEPT. TOTALS // TOTALS column // echo "<td align='right'><font style='color: $color; font-weight:bold;'>" . $total0 . "</font></td>"; // // PTO CALC $nonPTOtotalr = $ts_db->exec_statement($nonPTOtotalP, array($periodID, $end, $emp_no)); $nonPTOtotal = $ts_db->fetch_row($nonPTOtotalr); $ptoAcc = $row['JobTitle'] == 'STAFF' ? $nonPTOtotal[0] * 0.075 : 0; echo "<td align='right'>" . number_format($ptoAcc, 2) . "</td>"; echo "<td align='right'><font style='color: {$color}; font-weight:bold;'>" . $total0 . "</font></td>"; // // OVERTIME // $otime1 = array(); $otime2 = array(); foreach ($p as $v) { $weekoneR = $ts_db->exec_statement($weekoneP, array($emp_no, $v)); $weektwoR = $ts_db->exec_statement($weektwoP, array($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 = array_sum($otime1); $ot2 = array_sum($otime2); $otime = $ot1 + $ot2; // print_r($p); echo "<td align='right'>" . $otime . "</td>"; $otime = 0; $otime1 = array(); $otime2 = array(); // END OVERTIME echo "</tr>"; } echo "</tbody></table>\n"; } } // end 'run' button if ($this->current_user) { echo "<div class='log_btn'><a href='" . $FANNIE_URL . "auth/ui/loginform.php?logout=1'>logout</a></div>"; } else { echo "<div class='log_btn'><a href='" . $_SERVER["PHP_SELF"] . "?login=1'>login</a></div>"; // class='loginbox' } }
function show_sheet($emp_no, $periodID) { global $FANNIE_PLUGIN_SETTINGS, $FANNIE_OP_DB; $ts_db = FannieDB::get($FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']); include './includes/header.html'; $ft = 40; $query = $ts_db->prepare_statement("\n SELECT ROUND(SUM(hours), 2),\n date_format(t.tdate, '%a %b %D'),\n t.emp_no,\n e.firstName,\n date_format(p.periodStart, '%M %D, %Y'),\n date_format(p.periodEnd, '%M %D, %Y'),\n t.tdate\n FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.timesheet AS t\n INNER JOIN {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.TimesheetEmployees AS e ON (t.emp_no = e.timesheetEmployeeID)\n INNER JOIN {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.payperiods AS p ON (t.periodID = p.periodID)\n WHERE t.emp_no = ?\n AND t.area <> 31\n AND t.periodID = ?\n AND (t.vacation IS NULL OR t.vacation = 0)\n GROUP BY t.tdate"); $periodQ = $ts_db->prepare_statement("SELECT periodStart, periodEnd FROM \n {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.payperiods \n WHERE periodID = ?"); $periodR = $ts_db->exec_statement($periodQ, array($periodID)); list($periodStart, $periodEnd) = $ts_db->fetch_row($periodR); $weekoneQ = $ts_db->prepare_statement("\n SELECT ROUND(SUM(hours), 2)\n FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.timesheet AS t\n INNER JOIN {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.payperiods AS p ON (p.periodID = t.periodID)\n WHERE t.emp_no = ?\n AND t.periodID = ?\n AND t.area <> 31\n AND t.tdate >= DATE(p.periodStart)\n AND t.tdate < DATE(date_add(p.periodStart, INTERVAL 7 day))"); $weekoneR = $ts_db->exec_statement($weekoneQ, array($emp_no, $periodID)); $weektwoQ = $ts_db->prepare_statement("\n SELECT ROUND(SUM(hours), 2)\n FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.timesheet AS t\n INNER JOIN {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.payperiods AS p ON (p.periodID = t.periodID)\n WHERE t.emp_no = ?\n AND t.periodID = ?\n AND t.area <> 31\n AND t.tdate >= DATE(date_add(p.periodStart, INTERVAL 7 day)) \n AND t.tdate <= DATE(p.periodEnd)"); $weektwoR = $ts_db->exec_statement($weektwoQ, array($emp_no, $periodID)); $vacationQ = "\n SELECT ROUND(hours, 2), \n ID\n FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.timesheet AS t\n WHERE t.emp_no = {$emp_no}\n AND t.periodID = {$periodID}\n AND t.area = 31"; $employee = new TimesheetEmployeesModel($ts_db); $employee->timesheetEmployeeID($emp_no); $employee->load(); $Wage = $employee->wage(); if (is_null($Wage)) { $Wage = 0; } $name = $employee->firstName(); 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; } $vacation = 0; $vacationID = 0; if ($ts_db->num_rows($vacationR) != 0) { list($vacation, $vacationID) = $ts_db->fetch_row($vacationR); } /** I merged two sections a bit here rather than having separate sections for when $query finds rows and when it doesn't */ echo "<p>Timesheet for {$name} from " . date_format(date_create($periodStart), 'F dS, Y') . " to " . date_format(date_create($periodEnd), 'F dS, Y') . ": \n <span style='font-size:0.9em;'><a href='TsStaffMemReport.php?emp_no={$emp_no}&period={$periodID}&run=run'>View \n Staff Member Totals</a></span>\n </p>\n <table class=\"table table-striped table-bordered\">\n <tr><th>Date</th><th>Total Hours Worked</th><th></th></tr>\n"; $result = $ts_db->exec_statement($query, array($emp_no, $periodID)); $periodHours = 0.0; while ($row = $ts_db->fetch_array($result)) { 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>\n <a href=\"EditTimesheetDatePage.php?emp_no={$emp_no}&date={$row['6']}&periodID={$periodID}\">(Edit)</a></td></tr>\n"; $first = FALSE; $periodHours += $row[0]; } if ($periodHours == 0.0) { echo '<tr><td colspan="3">No hours this period</td></tr>'; } $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>\n <form action='" . $_SERVER['PHP_SELF'] . "' method='POST'>\n <p>Total hours in this pay period: " . number_format($periodHours, 2) . "</p>\n <table border=0 cellpadding='5'><tr><td>Week One: </td><td>"; if ($weekone > $ft) { echo '<font color="red">'; $font = '</font>'; } else { $font = NULL; } echo number_format($weekone, 2) . $font . "</td>"; $ot1 = $weekone - $ft > 0 ? $weekone - $ft : 0; if ($ot1 > 0) { echo "<td>OT: {$ot1}</td>"; } echo "</tr>"; echo "<tr><td>Week Two: </td><td>"; if ($weektwo > $ft) { echo '<font color="red">'; $font = '</font>'; } else { $font = NULL; } echo number_format($weektwo, 2) . $font . "</td>"; $ot2 = $weektwo - $ft > 0 ? $weektwo - $ft : 0; if ($ot2 > 0) { echo "<td>OT: {$ot2}</td>"; } echo "</tr>"; // echo "<td><b>Coming Soon-</b>Amount House Charged: $" . number_format($houseCharge, 2) . "</td></tr>"; echo "</tr><tr><td>Paid Time Off (PTO): </td><td>"; echo "<input type='text' name='vacation' size='5' maxlength='5' value='" . number_format($vacation, 2) . "' />" . $font . "\n <input type='hidden' name='vacationID' value='{$vacationID}' />\n <input type='hidden' name='period' value='{$periodID}' />\n <input type='hidden' name='emp' value='{$emp_no}' /></td>\n <!-- <td><button name='addvaca' type='submit'>Use PTO Hours</button></td> -->\n </tr>"; $otime = ($ot1 + $ot2) * 1.5; $week1 = $weekone > $ft ? $ft : $weekone; $week2 = $weektwo > $ft ? $ft : $weektwo; $gw = $Wage * ($week1 + $week2 + $vacation + $otime); echo "<tr><td>Gross Wages (before taxes): </td><td>\$" . number_format($gw, 2) . "</td></tr>"; echo "</table></form><br />"; }
public function get_id_view() { $dbc = $this->connection; $settings = $this->config->get('PLUGIN_SETTINGS'); $dbc->selectDB($settings['TimesheetDatabase']); $model = new TimesheetEmployeesModel($dbc); $model->timesheetEmployeeID($this->id); if (!$model->load()) { return '<div class="alert alert-danger">Employee does not exist</div>'; } $ret = '<h3>Employee #' . $this->id . '</h3> <form method="post"> <input type="hidden" name="id" value="' . $this->id . '" />'; $ret .= sprintf(' <div class="form-group"> <label>First Name</label> <input type="text" class="form-control" name="firstName" value="%s" /> </div>', $model->firstName()); $ret .= sprintf(' <div class="form-group"> <label>Last Name</label> <input type="text" class="form-control" name="lastName" value="%s" /> </div>', $model->lastName()); $ret .= '<div class="form-group"> <label>Active</label> <select name="active" class="form-control"> <option ' . ($model->active() ? 'selected' : '') . ' value="1">Yes</option> <option ' . (!$model->active() ? 'selected' : '') . ' value="0">No</option> </select> </div>'; $ret .= sprintf(' <div class="form-group"> <label>Hire Date</label> <input type="text" class="form-control date-field" name="hireDate" value="%s" /> </div>', $model->hireDate()); $ret .= '<div class="form-group"> <label>Timesheet Department</label> <select name="timesheetDepartmentID" class="form-control">'; $depts = new TimesheetDepartmentsModel($dbc); $ret .= $depts->toOptions($model->timesheetDepartmentID()); $ret .= '</select></div>'; $ret .= '<div class="form-group"> <label>Primary Job Position</label> <select name="primaryShiftID" class="form-control">'; $depts = new ShiftsModel($dbc); $ret .= $depts->toOptions($model->primaryShiftID()); $ret .= '</select></div>'; $ret .= sprintf(' <div class="form-group"> <label>Wage</label> <input type="text" class="form-control" name="wage" value="%.2f" /> </div>', $model->wage()); $ret .= sprintf(' <div class="form-group"> <label>Office Username</label> <input type="text" class="form-control" name="username" value="%s" /> </div>', $model->username()); $ret .= sprintf(' <div class="form-group"> <label>POS Member ID</label> <input type="text" class="form-control" name="posMemberID" value="%s" /> </div>', $model->posMemberID()); $ret .= sprintf(' <div class="form-group"> <label>Payroll Provider ID</label> <input type="text" class="form-control" name="payrollProviderID" value="%s" /> </div>', $model->payrollProviderID()); $ret .= sprintf(' <div class="form-group"> <label>Timeclock Token</label> <input type="text" class="form-control" name="timeclockToken" value="%s" /> </div>', $model->timeclockToken()); $ret .= '<p> <button type="submit" class="btn btn-default">Save Changes</button> <a href="' . $_SERVER['PHP_SELF'] . '" class="btn btn-default">All Employees</a> </p> </form>'; return $ret; }