function showStaffTotals($option)
{
// Show hours logged for all staff transactions,
// and commissions for Al
$db =& JFactory::getDBO();
global $mainframe;
$curYear = date('Y');
$curMonth = date('m');
$lastDayInMonth = date('t');
$dateStartDefault = "{$curYear}-{$curMonth}-01";
$dateEndDefault = "{$curYear}-{$curMonth}-{$lastDayInMonth}";
// not working w/ this php version?
//$dateStartDefault = date("Y-m-d", strtotime("first day of this month"));
//$dateEndDefault = date("Y-m-d", strtotime("first day of next month"));
$dateStart = $mainframe->getUserStateFromRequest($option . '.staff.dateStart', 'dateStart', $dateStartDefault, 'string');
$dateEnd = $mainframe->getUserStateFromRequest($option . '.staff.dateEnd', 'dateEnd', $dateEndDefault, 'string');
if ($dateStart == "") {
$dateStart = $dateStartDefault;
}
if ($dateEnd == "") {
$dateEnd = $dateEndDefault;
}
$dateStart = date('Y-m-d', strtotime($dateStart));
$dateEnd = date('Y-m-d', strtotime($dateEnd));
// Look up all staff hours logged this period, by user
$queryHours = "SELECT\n t.memberID,\n m.nameFirst,\n m.nameLast,\n SUM(t.totalTime) AS totalTimeSeconds,\n COUNT(*) AS clockins,\n MAX( t.totalTime ) AS longestClockIn,\n AVG( t.totalTime ) AS avgClockIn\n FROM\n #__cbodb_transactions t,\n #__cbodb_members m\n WHERE\n t.type = 4 AND\n t.dateOpen >= '{$dateStart} 00:00:00' AND\n t.dateOpen <= '{$dateEnd} 23:59:59' AND\n t.memberID = m.id\n GROUP BY\n t.memberID\n ORDER BY\n m.nameLast, m.nameFirst ASC";
$db->setQuery($queryHours);
$rowsHours = $db->loadObjectList();
if ($db->getErrorNum()) {
echo $db->stderr();
return false;
}
// Look up all commissions earned this period, by user
// Right now just getting Al's, since that's all we seem to care about
$queryCommissions = "SELECT\n \tcommissionUserID,\n \tnameFirst,\n \tnameLast,\n \tSUM(cash) AS commissionCash,\n \tSUM(credits) AS commissionCredits\n FROM\n\t((SELECT\n\t i.commissionUserID AS commissionUserID,\n\t m.nameFirst AS nameFirst,\n\t m.nameLast AS nameLast,\n\t\t t.cash AS cash,\n\t\t t.credits AS credits\n\t FROM\n\t #__cbodb_transactions t,\n\t #__cbodb_items i,\n\t #__cbodb_members m\n\t WHERE\n\t t.type = 1001 AND\n\t i.commissionUserID <> 0 AND\n\t t.itemID = i.tag AND\n\t t.dateOpen >= '{$dateStart} 00:00:00' AND\n\t t.dateOpen <= '{$dateEnd} 23:59:59' AND\n\t m.id = i.commissionUserID) ct)\n\tGROUP BY commissionUserID";
$db->setQuery($queryCommissions);
$rowsCommissions = $db->loadObjectList();
if ($db->getErrorNum()) {
echo $db->stderr();
return false;
}
// Merge commissions rows into hours rows
foreach ($rowsCommissions as $cRow) {
$found = false;
$hRowIndex = 0;
foreach ($rowsHours as $hRow) {
if ($hRow->memberID == $cRow->commissionUserID) {
$found = true;
break;
} else {
$hRowIndex++;
}
}
if (!$found) {
// Member with commissions doesn't have staff hours: add them
$hRowIndex = count($rowsHours);
$rowsHours[$hRowIndex] = new stdClass();
$rowsHours[$hRowIndex]->memberID = $cRow->commissionUserID;
$rowsHours[$hRowIndex]->nameFirst = $cRow->nameFirst;
$rowsHours[$hRowIndex]->nameLast = $cRow->nameLast;
}
$rowsHours[$hRowIndex]->commissionCash = $cRow->commissionCash;
$rowsHours[$hRowIndex]->commissionCredits = $cRow->commissionCredits;
}
HTML_cbodb::showStaffTotals($option, $rowsHours, $dateStart, $dateEnd);
}
