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