function generate_xls($month, $year, $userid, $courseid, $method = 'I', $base = '') { global $CFG, $DB; $monthinfo = get_month_info($month, $year); $workerrecord = $DB->get_record('block_timetracker_workerinfo', array('id' => $userid, 'courseid' => $courseid)); if (!$workerrecord) { print_error('usernotexist', 'block_timetracker', $CFG->wwwroot . '/blocks/timetracker/index.php?id=' . $courseid); } //TODO -- change this to a filename, comment out 'send' function $fn = $year . '_' . ($month < 10 ? '0' . $month : $month) . 'Timesheet_' . substr($workerrecord->firstname, 0, 1) . $workerrecord->lastname . '_' . $workerrecord->mdluserid . '.xls'; if ($method == 'F') { $workbook = new MoodleExcelWorkbook($base . '/' . $fn); } else { $workbook = new MoodleExcelWorkbook('-'); $workbook->send($fn); } // Formatting $format_bold =& $workbook->add_format(); $format_bold->set_bold(); $format_cal_block =& $workbook->add_format(); $format_cal_block->set_left(1); $format_cal_block->set_right(1); $format_cal_block->set_bottom(1); $format_cal_block->set_text_wrap(); $format_cal_block->set_v_align('Top'); $format_cal_block->set_size(8); $format_cal_total =& $workbook->add_format(); $format_cal_total->set_align('center'); $format_cal_total->set_bold(); $format_cal_total->set_size(12); $format_cal_total->set_left(1); $format_cal_total->set_right(1); $format_cal_total->set_bottom(1); $format_calendar_dates =& $workbook->add_format(); $format_calendar_dates->set_bold(); $format_calendar_dates->set_align('center'); $format_calendar_dates->set_size(8); $format_calendar_dates->set_left(1); $format_calendar_dates->set_right(1); $format_calendar_days =& $workbook->add_format(); $format_calendar_days->set_bold(); $format_calendar_days->set_align('center'); $format_calendar_days->set_size(8); $format_calendar_days->set_fg_color(22); $format_calendar_days->set_border(1); $format_center =& $workbook->add_format(); $format_center->set_align('center'); $format_footer =& $workbook->add_format(); $format_footer->set_bold(); $format_footer->set_bottom(1); $format_footer->set_v_align('Top'); $format_footer->set_text_wrap(); $format_footer_block =& $workbook->add_format(); $format_footer_block->set_bottom(1); $format_footer_block->set_top(1); $format_footer_block->set_left(1); $format_footer_block->set_right(1); $format_timesheet_header =& $workbook->add_format(); $format_timesheet_header->set_bold(); $format_timesheet_header->set_align('center'); $format_timesheet_header->set_size(12); $format_timesheet_header->set_bottom(1); $format_title =& $workbook->add_format(); $format_title->set_bold(); $format_title->set_align('center'); $format_title->set_size(12); $format_week_header =& $workbook->add_format(); $format_week_header->set_bold(); $format_week_header->set_align('center'); $format_week_header->set_size(8); // Collect Data $mdluser = $DB->get_record('user', array('id' => $workerrecord->mdluserid)); $conf = get_timetracker_config($courseid); $worksheet = array(); $worksheet[1] =& $workbook->add_worksheet('First worksheet'); // Set column widths $worksheet[1]->set_column(0, 8, 10.57); // Write data to spreadsheet $worksheet[1]->write_string(0, 0, 'Mars Hill College', $format_title); $worksheet[1]->merge_cells(0, 0, 0, 7); $worksheet[1]->write_string(1, 0, 'Timesheet - ' . $monthinfo['monthname'] . ', ' . $year, $format_timesheet_header); $worksheet[1]->merge_cells(1, 0, 1, 7); // Creates separator line under 'Timesheet' foreach (range(1, 7) as $i) { $worksheet[1]->write_blank(1, $i, $format_timesheet_header); } // Header Data $worksheet[1]->write_string(2, 0, 'WORKER: ' . strtoupper($workerrecord->lastname) . ', ' . strtoupper($workerrecord->firstname), $format_bold); $worksheet[1]->merge_cells(2, 0, 2, 3); $worksheet[1]->write_string(3, 0, "ID: {$mdluser->username}", $format_bold); $worksheet[1]->merge_cells(3, 0, 3, 3); $worksheet[1]->write_string(4, 0, "ADDRESS: {$workerrecord->address}", $format_bold); $worksheet[1]->merge_cells(4, 0, 4, 3); $worksheet[1]->write_string(5, 0, 'YTD Earnings: $' . number_format(get_earnings_this_year($userid, $courseid), 2), $format_bold); $worksheet[1]->merge_cells(5, 0, 5, 3); $worksheet[1]->write_string(2, 4, 'SUPERVISOR: ' . $conf['supname'], $format_bold); $worksheet[1]->merge_cells(2, 4, 2, 7); $worksheet[1]->write_string(3, 4, 'DEPARTMENT: ' . $conf['department'], $format_bold); $worksheet[1]->merge_cells(3, 4, 3, 7); $worksheet[1]->write_string(4, 4, 'POSITION: ' . $conf['position'], $format_bold); $worksheet[1]->merge_cells(4, 4, 4, 7); $worksheet[1]->write_string(5, 4, 'BUDGET: ' . $conf['budget'], $format_bold); $worksheet[1]->merge_cells(5, 4, 5, 7); // Calendar Data $worksheet[1]->write_string(7, 0, 'Sunday', $format_calendar_days); $worksheet[1]->write_string(7, 1, 'Monday', $format_calendar_days); $worksheet[1]->write_string(7, 2, 'Tuesday', $format_calendar_days); $worksheet[1]->write_string(7, 3, 'Wednesday', $format_calendar_days); $worksheet[1]->write_string(7, 4, 'Thursday', $format_calendar_days); $worksheet[1]->write_string(7, 5, 'Friday', $format_calendar_days); $worksheet[1]->write_string(7, 6, 'Saturday', $format_calendar_days); $worksheet[1]->write_string(7, 7, 'Total Hours', $format_calendar_days); $worksheet[1]->set_row(9, 69); $worksheet[1]->set_row(11, 69); $worksheet[1]->set_row(13, 69); $worksheet[1]->set_row(15, 69); $worksheet[1]->set_row(17, 69); $worksheet[1]->set_row(19, 69); $worksheet[1]->set_row(8, 11.25); $worksheet[1]->set_row(10, 11.25); $worksheet[1]->set_row(12, 11.25); $worksheet[1]->set_row(14, 11.25); $worksheet[1]->set_row(16, 11.25); $worksheet[1]->set_row(18, 11.25); foreach (range(0, 7) as $i) { $worksheet[1]->write_blank(8, $i, $format_calendar_dates); $worksheet[1]->write_blank(9, $i, $format_cal_block); $worksheet[1]->write_blank(10, $i, $format_calendar_dates); $worksheet[1]->write_blank(11, $i, $format_cal_block); $worksheet[1]->write_blank(12, $i, $format_calendar_dates); $worksheet[1]->write_blank(13, $i, $format_cal_block); $worksheet[1]->write_blank(14, $i, $format_calendar_dates); $worksheet[1]->write_blank(15, $i, $format_cal_block); $worksheet[1]->write_blank(16, $i, $format_calendar_dates); $worksheet[1]->write_blank(17, $i, $format_cal_block); $worksheet[1]->write_blank(18, $i, $format_calendar_dates); $worksheet[1]->write_blank(19, $i, $format_cal_block); } // Footer foreach (range(0, 7) as $i) { $worksheet[1]->write_blank(20, $i, $format_footer_block); $worksheet[1]->write_blank(21, $i, $format_footer_block); } // Number the Days and add data $units = get_split_month_work_units($workerrecord->id, $courseid, $month, $year); $date = 1; $dayofweek = $monthinfo['dayofweek']; $weeksum = 0; $monthsum = 0; for ($currentrow = 8; $currentrow < 20; $currentrow += 2) { //echo "inside for loop <br />"; $dayofweek = $dayofweek % 7; do { $worksheet[1]->write_string($currentrow, $dayofweek, $date, $format_calendar_dates); //begin of print work units // Print the data in the correct date blocks $wustr = ""; $mid = 86400 * ($date - 1) + $monthinfo['firstdaytimestamp']; $eod = 86400 * ($date - 1) + ($monthinfo['firstdaytimestamp'] + 86399); if ($units) { foreach ($units as $unit) { if ($unit->timein < $eod && $unit->timein >= $mid) { $in = userdate($unit->timein, get_string('timeformat', 'block_timetracker')); $out = userdate($unit->timeout, get_string('timeformat', 'block_timetracker')); if ($unit->timeout - $unit->timein > 449) { $wustr .= "In: {$in}\nOut: {$out}\n"; $weeksum += get_hours($unit->timeout - $unit->timein); } } } } $worksheet[1]->write_string($currentrow + 1, $dayofweek, $wustr, $format_cal_block); //end of print work units //if day of week = 7, copy value over and reset weekly sum to 0. // Calculate total hours if ($dayofweek == 6 || $date == $monthinfo['lastday']) { //Add week sum to monthly sum //Print value in weekly totals column //clear weekly sum $monthsum = $monthsum + $weeksum; $worksheet[1]->write_string($currentrow + 1, 7, $weeksum, $format_cal_total); $weeksum = 0; } $dayofweek++; $date++; } while ($date <= $monthinfo['lastday'] && $dayofweek % 7 != 0); if ($date >= $monthinfo['lastday']) { break; } } // Write footer data $worksheet[1]->write_string(20, 0, "Pay Rate or Stipend Amount\n" . '$' . number_format($workerrecord->currpayrate, 2), $format_footer); $worksheet[1]->merge_cells(20, 0, 20, 3); $worksheet[1]->write_string(20, 4, 'Total Hours/Earnings for ' . $monthinfo['monthname'] . ', ' . $year . ":\n" . number_format($monthsum, 2) . ' / $' . $monthsum * $workerrecord->currpayrate, $format_footer); $worksheet[1]->merge_cells(20, 4, 20, 7); $worksheet[1]->write_string(21, 0, 'Supervisor Signature/Date', $format_footer); $worksheet[1]->merge_cells(21, 0, 21, 3); $worksheet[1]->write_string(21, 4, 'Worker Signature/Date', $format_footer); $worksheet[1]->merge_cells(21, 4, 21, 7); $worksheet[1]->set_row(20, 30); $worksheet[1]->set_row(21, 42); $workbook->close(); return $fn; }
/** * Gives an array with worker stats: * $stats['totalhours'] * $stats['monthhours' * $stats['yearhours'] * $stats['termhours'] * $stats['totalearnings'] * $stats['monthearnings'] * $stats['yearearnings'] * $stats['termearnings'] * @return an array with useful values */ function get_worker_stats($userid, $courseid) { global $DB; $stats['totalhours'] = get_total_hours($userid, $courseid); $stats['monthhours'] = get_hours_this_month($userid, $courseid); $stats['yearhours'] = get_hours_this_year($userid, $courseid); $stats['termhours'] = get_hours_this_term($userid, $courseid); $stats['totalearnings'] = get_total_earnings($userid, $courseid); $stats['monthearnings'] = get_earnings_this_month($userid, $courseid); $stats['yearearnings'] = get_earnings_this_year($userid, $courseid); $stats['termearnings'] = get_earnings_this_term($userid, $courseid); return $stats; }
/** @return an array of HTML pages used for printing - one page per array item */ function generate_html($start, $end, $userid, $courseid, $timesheetid = -1, $unsignedonly = false) { global $CFG, $DB; $pages = array(); $startstring = userdate($start, "%m%Y"); $endstring = userdate($end, "%m%Y"); $samemonth = $startstring == $endstring; $workerrecord = $DB->get_record('block_timetracker_workerinfo', array('id' => $userid)); if (!$workerrecord) { print_error('usernotexist', 'block_timetracker', $CFG->wwwroot . '/blocks/timetracker/index.php?id=' . $courseid); } // Collect Data $conf = get_timetracker_config($courseid); $firstmonth = userdate($start, "%m"); $firstyear = userdate($start, "%Y"); $firstmonthinfo = make_timestamp($firstyear, $firstmonth, 1); //$curr = $start; $curr = $firstmonthinfo; $overallhoursum = 0; $overalldollarsum = 0; while ($curr <= $end) { $month = userdate($curr, "%m"); $year = userdate($curr, "%Y"); $monthinfo = get_month_info($month, $year); $mid = $monthinfo['firstdaytimestamp']; $eod = strtotime('+ 1 day', $mid); $eod -= 1; $monthhoursum = 0; $monthdollarsum = 0; $units = get_split_month_work_units($workerrecord->id, $courseid, $month, $year, $timesheetid, $unsignedonly); // Add Page /**DING DING DING**/ //$pdf->AddPage(); // ********** HEADER ********** // $htmldoc = ' <table style="margin-left: auto; margin-right: auto" cellspacing="0"' . 'cellpadding="0" width="540px"> <tr> <td align="center"><font size="10"><b>' . $conf['institution'] . '</b></font></td> </tr> <tr> <td align="center"><font size="10"><b>Timesheet - ' . $monthinfo['monthname'] . ', ' . $year . '</b></font> </td> </tr> </table> <hr style="height: 1px" />'; //$pdf->writeHTML($htmldoc, true, false, false, false, ''); // ********** WORKER AND SUPERVISOR DATA ********** // $htmldoc .= ' <table style="margin-left: auto: margin-right: auto" cellspacing="0"' . 'cellpadding="0" width="540px"> <tr> <td><font size="8.5"><b>WORKER: ' . strtoupper($workerrecord->lastname) . ', ' . strtoupper($workerrecord->firstname) . '<br />' . 'ID: ' . $workerrecord->idnum . '<br />' . 'ADDRESS: ' . $workerrecord->address . '<br /> YTD Earnings: $ ' . get_earnings_this_year($userid, $courseid) . '</b></font></td> <td><font size="8.5"><b>SUPERVISOR: ' . $conf['supname'] . '<br />' . 'DEPARTMENT: ' . $conf['department'] . '<br />' . 'POSITION: ' . $conf['position'] . '<br />' . 'BUDGET: ' . $conf['budget'] . '</b></font></td> </tr> </table> <br />'; //$pdf->writeHTML($htmldoc, true, false, false, false, ''); // ********** CALENDAR DAYS HEADER (Sun - Sat) ********** // // ********** CALENDAR DATES AND DATA ********** // //Arrays for dates and vals; $days = array(); $vals = array(); $date = 1; $dayofweek = $monthinfo['dayofweek']; $weeksum = 0; $monthhoursum = 0; $htmldoc .= ' <table border="1" cellpadding="2px" width="540px" ' . 'style="margin-right: auto; margin-left: auto"> <tr bgcolor="#C0C0C0"> <td class="calendar" align="center"><font size="8"><b>Monday</b></font></td> <td class="calendar" align="center"><font size="8"><b>Tuesday</b></font></td> <td class="calendar" align="center"><font size="8"><b>Wednesday</b></font></td> <td class="calendar" align="center"><font size="8"><b>Thursday</b></font></td> <td class="calendar" align="center"><font size="8"><b>Friday</b></font></td> <td class="calendar" align="center"><font size="8"><b>Saturday</b></font></td> <td class="calendar" align="center"><font size="8"><b>Sunday</b></font></td> <td class="calendar" align="center"><font size="8"><b>Total Hours</b>' . '</font></td> </tr> '; // ********** START THE TABLE AND DATA ********** // //write blank cells to catch up to the first day of the month $counter = 1; while ($counter != $dayofweek) { $counter++; $days[] = '<td class="calendar" style="height: 10px"> </td>'; $vals[] = '<td class="calendar" style="height: 70px"> </td>'; $counter %= 7; } //a "week" - a row in the table for ($row = 0; $row < 6; $row++) { $dayofweek = $dayofweek % 7; do { $days[] = '<td class="calendar" style="height: 10px" align="center"><b>' . $date . '</b></td>'; //begin of print work units // Print the data in the correct date blocks $wustr = ""; if ($units) { foreach ($units as $unit) { if ($unit->timein < $eod && $unit->timein >= $mid && $unit->timein >= $start && $unit->timeout <= $end) { $in = userdate($unit->timein, get_string('timeformat', 'block_timetracker')); $out = userdate($unit->timeout, get_string('timeformat', 'block_timetracker')); //FIXMEFIXME! if (array_key_exists('round', $conf) && $conf['round'] > 0) { $factor = $conf['round'] / 2 - 1; } else { $factor = 0; } if ($unit->timeout - $unit->timein > $factor) { //WHAT IF NOT ROUNDED? $wustr .= "In: {$in}<br />Out: {$out}<br />"; $hours = get_hours($unit->timeout - $unit->timein, $unit->courseid); //overtime or regular? if ($hours + $weeksum > 40) { $ovthours = $reghours = 0; if ($weeksum > 40) { //already over 40 //no reghours, just ovthours $ovthours = $hours; } else { //not already over 40 $reghours = 40 - $weeksum; $ovthours = $hours - $reghours; } $amt = $reghours * $unit->payrate; $ovtamt = $ovthours * ($workerrecord->currpayrate * 1.5); $amt += $ovtamt; } else { $amt = $hours * $unit->payrate; } $monthdollarsum += $amt; $overalldollarsum += $amt; $weeksum += $hours; $overallhoursum += $hours; } } } } $vals[] = '<td class="calendar" style="height: 70px"><font size="7">' . $wustr . '</font></td>'; //if day of week = 0 (Sunday), copy value over and reset weekly sum to 0. // Calculate total hours if ($dayofweek == 0) { //Add week sum to monthly sum //Print value in weekly totals column //clear weekly sum $monthhoursum += $weeksum; $days[] = '<td class="calendar" style="height: 10px"> </td>'; if ($weeksum == 0) { $weeksum = ' '; } $vals[] = '<td class="calendar" style="height: 70px" align="center">' . '<font size="10"><b><br /><br />' . $weeksum . '</b><br /></font></td>'; $weeksum = 0; } else { if ($date == $monthinfo['lastday']) { //what about when we reach the end of the month? //Still need to put totals!!! $counter = $dayofweek; while ($counter != 0) { //pad to the rightmost column $days[] = '<td class="calendar" style="height: 10px"> </td>'; $vals[] = '<td class="calendar" style="height: 70px"> </td>'; $counter++; $counter %= 7; } $monthhoursum += $weeksum; $days[] = '<td class="calendar" style="height: 10px"> </td>'; if ($weeksum == 0) { $weeksum = ' '; } $vals[] = '<td class="calendar" style="height: 70px" align="center">' . '<font size="10"><b><br /><br />' . $weeksum . '</b><br /></font></td>'; $weeksum = 0; } } $mid = strtotime('+ 1 day', $mid); //midnight $eod = strtotime('+ 1 day', $eod); //23:59:59 $dayofweek++; $date++; $curr = strtotime('+1 day', $curr); } while ($date <= $monthinfo['lastday'] && $dayofweek != 7); if ($date >= $monthinfo['lastday']) { break; } } //this is a single "row" or "week" for ($i = 0; $i < 6; $i++) { $htmldoc .= "\n<tr>\n"; for ($j = 0; $j < 8; $j++) { $spot = $j + 8 * $i; if (isset($days[$spot])) { $htmldoc .= "\t" . $days[$spot] . "\n"; } else { $htmldoc .= "\t" . '<td class="calendar" style="height: 10px"> </td>' . "\n"; } } $htmldoc .= "\n</tr>\n"; $htmldoc .= "\n<tr>\n"; for ($j = 0; $j < 8; $j++) { $spot = $j + 8 * $i; if (isset($vals[$spot])) { $htmldoc .= "\t" . $vals[$spot] . "\n"; } else { $htmldoc .= "\t" . '<td class="calendar" style="height: 70px"> </td>' . "\n"; } } $htmldoc .= "\n</tr>\n"; } $htmldoc .= '</table><br />'; //$pdf->writeHTML($htmldoc, true, false, false, false, ''); // ********** FOOTER TOTALS ********** // $htmldoc .= ' <table border="1" cellpadding="5px" width="540px" ' . 'style="margin-left: auto; margin-right: auto"> <tr> <td style="height: 25px"><font size="13"><b>Base Pay Rate</b></font> <br /> <font size="10">$' . round($workerrecord->currpayrate, 2) . '</font></td> <td style="height: 20px"><font size="13"><b>Total Hours/Earnings for ' . $monthinfo['monthname'] . ', ' . $year . '</b></font><br /><font size="10">' . round($monthhoursum, 3) . ' / $' . round($monthdollarsum, 2) . '</font></td> </tr></table><br />'; //$pdf->writeHTML($htmldoc, true, false, false, false, ''); //here is a new page!!! $pages[] = $htmldoc; } end($pages); $key = key($pages); $htmldoc = $pages[$key]; // ********** OVERALL TOTALS AND SIGNATURES********** // if ($timesheetid != -1) { $ts = $DB->get_record('block_timetracker_timesheet', array('id' => $timesheetid)); } $htmldoc .= ' <table border="1" cellpadding="5px" width="540px" ' . 'style="margin-left: auto; margin-right: auto">'; if (!$samemonth) { $desc = ''; if ($timesheetid == -1) { $desc = userdate($start, get_string('dateformat', 'block_timetracker')) . ' to ' . userdate($end, get_string('dateformat', 'block_timetracker')); } $htmldoc .= ' <tr> <td colspan="2" style="height: 35px"><font size="13"><b>Total Hours/Earnings ' . $desc . '</b></font><br /><font size="10">' . round($overallhoursum, 3) . ' / $' . round($overalldollarsum, 2) . '</font></td> </tr>'; } if ($timesheetid != -1) { $datestr = get_string('datetimeformat', 'block_timetracker'); $htmldoc .= ' <tr> <td style="height: 45px"><font size="13"><b>Worker Signature/Date</b></font>' . '<br />' . '<font size="8">Signed by ' . $workerrecord->firstname . ' ' . $workerrecord->lastname . '<br />' . userdate($ts->workersignature, $datestr) . '</font></td>' . '<td style="height: 45px"><font size="13"><b>Supervisor Signature/Date</b>' . '</font><br />' . '<font size="8">'; if ($ts->supervisorsignature != 0) { $super = $DB->get_record('user', array('id' => $ts->supermdlid)); if (!$super) { print_error('Supervisor does not exist'); } $htmldoc .= 'Signed by ' . $super->firstname . ' ' . $super->lastname . '<br />' . userdate($ts->supervisorsignature, $datestr); } else { $htmldoc .= 'Awaiting supervisor signature'; } $htmldoc .= ' </font></td> </tr> </table><br />'; } else { /* $htmldoc .=' <tr> <td style="height: 45px"><font size="13"><b>Worker Signature/Date</b></font></td> <td style="height: 45px"><font size="13"><b>Supervisor Signature/Date</b></font></td> </tr> */ $htmldoc .= ' </table><br />'; } $pages[$key] = $htmldoc; return $pages; }