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;
}
