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 body_content() { global $FANNIE_OP_DB, $FANNIE_PLUGIN_SETTINGS, $FANNIE_URL; $ts_db = FannieDB::get($FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']); if ($this->display_func == 'ts_error') { return $this->error_contents(); } elseif ($this->display_func == 'ts_show') { return $this->show_sheet(FormLib::get_form_value('emp_no'), FormLib::get_form_value('period')); } include './includes/header.html'; echo '<form action="' . $_SERVER['PHP_SELF'] . '" method="POST">'; echo '<div class="form-group">'; if ($_SESSION['logged_in'] == True) { echo '<label>Name</label><select name="emp_no" class="form-control"> <option>Select staff member</option>'; $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>'; } else { echo "<label>Employee Number*</label>\n <input type='text' name='emp_no' size=4 \n class=\"form-control\" autocomplete='off' />"; } 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'), \n date_format(periodEnd, '%M %D, %Y'), 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="form-group">'; echo '<label>Pay Period</label><select name="period" class="form-control"> <option>Please select a payperiod to view.</option>'; while ($row = $ts_db->fetch_array($result)) { echo "<option value=\"{$row['2']}\""; if ($row[2] == $ID) { echo ' SELECTED'; } echo ">({$row['0']} - {$row['1']})</option>"; } echo '</select></div>'; echo '<div class="form-group">'; echo '<button name="submit" type="submit" class="btn btn-default">Submit</button> <input type="hidden" name="submitted" value="TRUE" /> </div></form>'; 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 body_content() { global $FANNIE_OP_DB, $FANNIE_PLUGIN_SETTINGS; $ts_db = FannieDB::get($FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']); if ($this->display_func == 'ts_error') { return $this->error_content(); } elseif ($this->display_func == 'ts_delete_msg') { return $this->delete_msg(); } include './includes/header.html'; $emp_no = FormLib::get_form_value('emp_no', ''); $date = FormLib::get_form_value('date', ''); $periodID = FormLib::get_form_value('periodID', ''); $employee = new TimesheetEmployeesModel($ts_db); $employee->timesheetEmployeeID($emp_no); $employee->load(); $name = $employee->firstName() . ' ' . $employee->lastName(); echo "<form action='" . $_SERVER['PHP_SELF'] . "' method='POST'>\n <input type='hidden' name='emp_no' value='{$emp_no}' />\n <input type='hidden' name='date' value='{$date}' />\n <input type='hidden' name='submitted' value='TRUE' />\n <p align='center'>\n <button class=\"btn btn-default\" name='submit' type='submit' value='delete'>Remove this day from my timesheet.</button>\n </p>\n </form>"; echo "<form action='" . $_SERVER['PHP_SELF'] . "' method='POST'>"; echo "<table class=\"table table-bordered\"><tr><td><p>Name: <strong>{$name}</strong></p></td><td><p>Date: <strong>" . substr($date, 0, 4) . "-" . substr($date, 5, 2) . "-" . substr($date, 8, 2) . "</strong></p></td></tr>\n <input type='hidden' name='emp_no' value='{$emp_no}' />\n <input type='hidden' name='periodID' value='{$periodID}' /> \n <input type='hidden' name='date' value='{$date}' />"; echo "<tr><td align='right'><b>Total Hours</b></td><td align='center'><strong>Labor Category</strong></td>\n <!--<td><strong>Remove</strong></td>--></tr>\n"; $max = 10; // Max number of entries. for ($i = 1; $i <= $max; $i++) { $inc = $i - 1; $query = $ts_db->prepare_statement("SELECT hours, area, ID \n FROM " . $FANNIE_PLUGIN_SETTINGS['TimesheetDatabase'] . ".timesheet \n WHERE emp_no = ? AND tdate = ? ORDER BY ID ASC LIMIT " . $inc . ",1"); // echo $query; $result = $ts_db->exec_statement($query, array($emp_no, $date)); $num = $ts_db->num_rows($result); if ($row = $ts_db->fetch_row($result)) { $hours = $row[0] ? $row[0] : ''; $area = $row[1]; $ID = $row[2]; } else { $hours = ''; $area = NULL; $ID = "insert"; } echo "<tr><td align='right'><input \n class=\"form-control price-field\" type='text' name='hours" . $i . "' value='{$hours}' size=6></input></td>"; $query = $ts_db->prepare_statement("SELECT IF(NiceName='', ShiftName, NiceName), ShiftID \n FROM " . $FANNIE_PLUGIN_SETTINGS['TimesheetDatabase'] . ".shifts \n WHERE visible=true ORDER BY ShiftOrder ASC"); $result = $ts_db->exec_statement($query); echo '<td><select name="area' . $i . '" id="area' . $i . '" class="form-control"><option>Please select an area of work.</option>'; while ($row = $ts_db->fetch_row($result)) { echo "<option id =\"{$i}{$row['1']}\" value=\"{$row['1']}\" "; if ($row[1] == $area) { echo "SELECTED"; } echo ">{$row['0']}</option>"; } echo "</select><input type='hidden' name='ID" . $i . "' value='{$ID}' /></td>"; echo "</tr>\n"; } echo '<tr><td colspan=2 align="center"> <button name="submit" class="btn btn-default" type="submit" value="submit"'; // echo "onclick='confirm('Do you really want to DELETE hours?')' "; echo '>Submit</button> <input type="hidden" name="submitted" value="TRUE" /></td></tr>'; echo '</table></form>'; }
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_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 class="form-control" name="period"> <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></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->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 class="btn btn-default" value="run" name="run">Run</button> </p> </form>'; if (FormLib::get_form_value('run') == 'run') { $periodID = FormLib::get_form_value('period', 0); $end = FormLib::get_form_value('end', $periodID); if ($end == 0) { $end = $periodID; } $employees = new TimesheetEmployeesModel($ts_db); $employees->active(1); $areasq = $ts_db->prepare_statement("SELECT ShiftName, ShiftID \n FROM " . $FANNIE_PLUGIN_SETTINGS['TimesheetDatabase'] . ".shifts \n WHERE visible = 1 AND ShiftID <> 31 ORDER BY ShiftOrder"); $areasr = $ts_db->exec_statement($areasq); $shiftInfo = array(); while ($row = $ts_db->fetch_row($areasr)) { $shiftInfo[$row['ShiftID']] = $row['ShiftName']; } $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 \n WHERE periodID = ?"); $result2 = $ts_db->exec_statement($query2, array($end)); $periodEnd = $ts_db->fetch_row($result2); // $periodct = ($end !== $periodID) ? $end - $periodID : 1; $periodct = 0; $p = array(); for ($i = $periodStart[1]; $i <= $periodEnd[1]; $i++) { // echo $i; $periodct++; $p[] = $i; } echo "<br />"; echo "<h3>" . $periodStart[0] . " — " . $periodEnd[0] . "</h3>\n"; echo "Number of payperiods: " . $periodct . "\n"; // // END TITLE echo "<br />"; echo "<table class=\"table table-bordered table-striped\"><thead>\n<tr><th>Name</th><th>Wage</th>"; foreach ($shiftInfo as $sID => $sName) { echo "<div id='vth'><th>" . substr($sName, 0, 6) . "</th></div>"; // -- TODO vertical align th, static col width } echo "</th><th>OT</th><th>PTO used</th><th>PTO new</th><th>Total</th></tr></thead>\n<tbody>\n"; $PTOnew = array(); $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) FROM \n {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.timesheet t \n WHERE t.periodID >= ? AND t.periodID <= ?\n AND t.emp_no = ? AND t.area = ?"); $weekoneQ = $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(p.periodStart)\n AND t.tdate < DATE(date_add(p.periodStart, INTERVAL 7 day))"); $weektwoQ = $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)"); $usedP = $ts_db->prepare_statement("SELECT SUM(hours) FROM " . $FANNIE_PLUGIN_SETTINGS['TimesheetDatabase'] . ".timesheet \n WHERE periodID >= ? AND periodID <= ? AND \n emp_no = ? AND area = 31"); $nonPTOtotalP = $ts_db->prepare_statement("SELECT SUM(hours) FROM " . $FANNIE_PLUGIN_SETTINGS['TimesheetDatabase'] . ".timesheet \n WHERE periodID >= ? AND periodID <= ?\n AND area <> 31 AND emp_no = ?"); foreach ($employees->find('lastName') as $employee) { $emp_no = $employee->timesheetEmployeeID(); $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>" . 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($periodID, $end, $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 // // OVERTIME // $otime = 0; $otime1 = 0; $otime2 = 0; foreach ($p as $v) { $weekoneR = $ts_db->exec_statement($weekoneQ, array($emp_no, $v)); $weektwoR = $ts_db->exec_statement($weektwoQ, 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; } $OT[] = $otime; echo "<td align='right'>" . $otime . "</td>"; // END OVERTIME // // PTO USED $usedR = $ts_db->exec_statement($usedP, array($periodID, $end, $emp_no)); $ptoused = $ts_db->fetch_row($usedR); $PTOuse = !$ptoused[0] ? 0 : number_format($ptoused[0], 2); echo "<td align='right'>{$PTOuse}</td>"; // // PTO CALC $nonPTOtotalr = $ts_db->exec_statement($nonPTOtotalP, array($periodID, $end, $emp_no)); $nonPTOtotal = $ts_db->fetch_row($nonPTOtotalr); $ptoAcc = $employee->primaryShiftID() ? $nonPTOtotal[0] * 0.075 : 0; echo "<td align='right'>" . number_format($ptoAcc, 2) . "</td>"; $PTOnew[] = $ptoAcc; // // TOTAL echo "<td align='right'><font style='color: {$color}; font-weight:bold;'>" . $total0 . "</font></td>"; echo "</tr>"; } echo "<tr><td colspan=2><b>TOTALS</b></td>"; $areasr = $ts_db->exec_statement($areasq); $TOT = array(); $query = $ts_db->prepare_statement("SELECT ROUND(SUM(t.hours),2) \n FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.timesheet t \n WHERE t.periodID BETWEEN ? AND ?\n AND t.area = ?"); foreach ($shiftInfo as $area => $shiftName) { // echo $query; $totsr = $ts_db->exec_statement($query, array($periodID, $end, $area)); $tots = $ts_db->fetch_row($totsr); $tot = !$tots[0] || $tots[0] == '' ? '0' : $tots[0]; echo "<td align='right'><b>{$tot}</b></td>"; $TOT[] = $tot; } $ptoq = $ts_db->prepare_statement("SELECT ROUND(SUM(t.hours),2) \n FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.timesheet t \n WHERE t.periodID BETWEEN ? AND ?\n AND t.area = 31"); $ptor = $ts_db->exec_statement($ptoq, array($periodID, $end)); $pto = $ts_db->fetch_row($ptor); $OTTOT = number_format(array_sum($OT), 2); echo "<td><b>{$OTTOT}</b></td>"; $PTOUSED = !$pto[0] || $pto[0] == '' ? '0' : $pto[0]; echo "<td><b>{$PTOUSED}</b></td>"; $PTOTOT = number_format(array_sum($PTOnew), 2); echo "<td><b>{$PTOTOT}</b></td>"; $TOTAL = number_format(array_sum($TOT), 2); echo "<td><b>{$TOTAL}</b></td>"; echo "</tr>"; echo "</tbody></table>\n"; } // end 'run' button }
public function get_view() { $dbc = $this->connection; $settings = $this->config->get('PLUGIN_SETTINGS'); $dbc->selectDB($settings['TimesheetDatabase']); $model = new TimesheetEmployeesModel($dbc); $ret = <<<HTML <table class="table table-bordered table-striped"> <thead> <tr> <th>#</th> <th>First Name</th> <th>Last Name</th> <th>Department</th> <th>Position</th> <th>View/Edit</th> </tr> </thead> <tbody> HTML; $depts = array(); $dModel = new TimesheetDepartmentsModel($dbc); foreach ($dModel->find() as $d) { $depts[$d->timesheetDepartmentID()] = $d->name(); } $shifts = array(); $sModel = new ShiftsModel($dbc); foreach ($sModel->find() as $s) { $shifts[$s->shiftID()] = $s->NiceName(); } foreach ($model->find('timesheetEmployeeID') as $obj) { $ret .= sprintf('<tr> <td>%d</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td><a href="?id=%d" class="btn btn-default btn-xs">%s</a></td> </tr>', $obj->timesheetEmployeeID(), $obj->firstName(), $obj->lastName(), isset($depts[$obj->timesheetDepartmentID()]) ? $depts[$obj->timesheetDepartmentID()] : '?', isset($shifts[$obj->primaryShiftID()]) ? $shifts[$obj->primaryShiftID()] : '?', $obj->timesheetEmployeeID(), \COREPOS\Fannie\API\lib\FannieUI::editIcon()); } $ret .= <<<HTML </tbody> </table> <form class="form-inline" method="post"> <p> <label>Timesheet ID</label> <input type="text" class="form-control" name="newID" placeholder="Optional - omit for automatic ID" title="Optional - omit for automatic ID" /> <button type="submit" class="btn btn-default">Create New Employee</button> </p> HTML; return $ret; }
function body_content() { global $FANNIE_OP_DB, $FANNIE_URL, $FANNIE_PLUGIN_SETTINGS; $ts_db = FannieDB::get($FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']); include './includes/header.html'; /** if preprocess() changed the setting for display_func based on form input, show that content instead of the default form */ if ($this->display_func == 'ts_success') { return $this->success_content(); } elseif ($this->display_func == 'ts_error') { return $this->error_content(); } echo '<form action="' . $_SERVER['PHP_SELF'] . '" method="POST" name="timesheet" id="timesheet">'; echo '<p><div class="form-inline">'; if ($this->current_user) { echo ' <div class="form-group"> Name: <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 "<div class=\"form-group\">\n Employee Number*: <input type='text' name='emp_no' class=\"form-control\"\n value='" . $_COOKIE['timesheet'] . "' size=4 autocomplete='off' />\n </div>"; } echo '<div class="form-group"> Date*: <input type="text" name="date" value="' . date('Y-m-d') . '" class="form-control date-field" size=10 class="datepicker" alt="Tip: try cmd + arrow keys" /> </div> </div></p>'; echo '<table class="table table-bordered">'; echo "<tr><td align='right'><b>Total Hours</b></td><td align='center'><strong>Labor Category</strong></td>"; $queryP = $ts_db->prepare_statement("SELECT IF(NiceName='', ShiftName, NiceName), ShiftID \n FROM " . $FANNIE_PLUGIN_SETTINGS['TimesheetDatabase'] . ".shifts \n WHERE visible=true ORDER BY ShiftOrder ASC"); $max = 5; for ($i = 1; $i <= $max; $i++) { echo "<tr><td align='right'><input class=\"form-control price-field\" \n type='text' name='hours" . $i . "' size=6></input></td>"; $result = $ts_db->exec_statement($queryP); echo '<td><select class="form-control" name="area' . $i . '" id="area' . $i . '"> <option>Please select an area of work.</option>'; while ($row = $ts_db->fetch_row($result)) { echo "<option id =\"{$i}{$row['1']}\" value=\"{$row['1']}\">{$row['0']}</option>"; } echo '</select></td></tr>' . "\n"; } echo '</table>'; echo '<p> <button name="submit" class="btn btn-default" type="submit">Submit</button> <input type="hidden" name="submitted" value="TRUE" /> </p></form>'; 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 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_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 class="form-control" name="period"> <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></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->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 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_statement("SELECT date_format(periodStart, '%M %D, %Y') as periodStart, periodID \n FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.payperiods 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, periodID \n FROM {$FANNIE_PLUGIN_SETTINGS['TimesheetDatabase']}.payperiods WHERE periodID = ?"); $result2 = $ts_db->exec_statement($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_statement("SELECT s.ShiftID as id, \n IF(s.NiceName='', s.ShiftName, s.NiceName) as area\n FROM (SELECT ShiftID, NiceName, ShiftName, ShiftOrder \n FROM " . $FANNIE_PLUGIN_SETTINGS['TimesheetDatabase'] . ".shifts WHERE visible = 1 AND ShiftID <> 31) s \n GROUP BY s.ShiftID ORDER BY s.ShiftOrder"); // echo $query; $result = $ts_db->exec_statement($query); echo "<table class=\"table table-bordered table-striped\"><thead>\n<tr>\n <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_statement("SELECT SUM(t.hours) as total \n FROM " . $FANNIE_PLUGIN_SETTINGS['TimesheetDatabase'] . ".timesheet t \n WHERE t.periodID >= ? AND t.periodID <= ? AND t.area = ?"); $query2P = $ts_db->prepare_statement("\n SELECT SUM(e.wage) as agg \n FROM " . $FANNIE_PLUGIN_SETTINGS['TimesheetDatabase'] . ".TimesheetEmployees e, " . $FANNIE_PLUGIN_SETTINGS['TimesheetDatabase'] . ".timesheet t \n WHERE t.emp_no = e.timesheetEmployeeID AND t.periodID >= ?\n AND t.periodID <= ? AND t.area = ?"); while ($row = $ts_db->fetch_row($result)) { echo "<tr><td>" . $row['id'] . "</td><td>" . $row['area'] . "</td><td align='right'>"; $result1 = $ts_db->exec_statement($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->exec_statement($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'>" . money_format('%n', $wages) . "</td></tr>\n"; $wageArray[] = $wages; // $wageArray = array(); // array_push($wageArray, $wages); // foreach ($wages as $w) { // $wageArray[] = $w; // } if ($row['id'] == "31") { $csvwages .= ""; } 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>\n <td align=right><b>" . number_format(array_sum($totArray), 2) . "</b></td>\n <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_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(p.periodStart)\n AND t.tdate < DATE(date_add(p.periodStart, INTERVAL 7 day))"); $weektwoQ = $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)"); foreach ($p as $v) { foreach ($employees->find() as $employee) { $row = array('emp_no' => $employee->timesheetEmployeeID()); $weekoneR = $ts_db->exec_statement($weekoneP, array($row['emp_no'], $v)); $weektwoR = $ts_db->exec_statement($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_statement("SELECT SUM(t.hours) as total FROM " . $FANNIE_PLUGIN_SETTINGS['TimesheetDatabase'] . ".timesheet t \n WHERE t.periodID >= ? AND t.periodID <= ? AND t.area = 31"); $ptoR = $ts_db->exec_statement($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_statement("SELECT SUM(hours) \n FROM " . $FANNIE_PLUGIN_SETTINGS['TimesheetDatabase'] . ".timesheet \n WHERE periodID >= ? AND periodID <= ? AND area <> 31 \n AND emp_no = ?"); $PTOnew = array(); foreach ($employees->find() as $employee) { $nonPTOtotalr = $ts_db->exec_statement($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>"; } }