function db_results($sql) { return fetch_rows(query($sql)); }
function getData($company_id, $subject, $type, $from_date, $to_date) { $cls_join_on = 'usr.username = lv.' . $type; $cls_date = "DATE(lv.at) BETWEEN '" . mysql_real_escape_string($from_date) . "' AND '" . mysql_real_escape_string($to_date) . "'"; $USERS = USERS; $LOVE = LOVE; if ($subject == 'love_within_team') { return fetch_rows("\n SELECT\n {$type}.team AS subject,\n COUNT(*) AS count\n FROM\n ({$LOVE} AS lv LEFT JOIN {$USERS} AS giver ON giver.username = lv.giver)\n LEFT JOIN {$USERS} AS receiver ON receiver.username = lv.receiver\n WHERE\n giver.company_id = {$company_id}\n AND receiver.company_id = {$company_id}\n AND giver.team = receiver.team\n AND {$cls_date}\n GROUP BY\n subject\n ORDER BY\n count DESC\n "); } elseif ($subject == 'love_between_teams') { return fetch_rows("\n SELECT\n {$type}.team AS subject,\n COUNT(*) AS count\n FROM\n ({$LOVE} AS lv LEFT JOIN {$USERS} AS giver ON giver.username = lv.giver)\n LEFT JOIN {$USERS} AS receiver ON receiver.username = lv.receiver\n WHERE\n giver.company_id = {$company_id}\n AND receiver.company_id = {$company_id}\n AND giver.team != receiver.team\n AND {$cls_date}\n GROUP BY\n subject\n ORDER BY\n count DESC\n "); } elseif ($subject == 'most_active_team_members') { $result = array(); foreach (array_keys(teams($company_id)) as $team) { $counts = fetch_rows("\n SELECT\n usr.username AS subject,\n COUNT(*) AS count\n FROM\n {$USERS} AS usr LEFT JOIN {$LOVE} AS lv\n ON {$cls_join_on}\n WHERE\n usr.company_id = {$company_id}\n AND usr.team = '{$team}'\n AND {$cls_date}\n GROUP BY\n subject\n ORDER BY\n count ASC\n "); $total = 0; foreach ($counts as $one) { $total += $one[1]; } $active = count($counts); if ($total) { $sum = 0; foreach ($counts as $one) { if (($sum + $one[1]) / $total > 0.2) { break; } $sum += $one[1]; $active--; } } $result[] = array($team, $active); } return $result; } else { return fetch_rows("\n SELECT\n usr.{$subject} AS subject,\n COUNT(*) AS count\n FROM\n {$USERS} AS usr LEFT JOIN {$LOVE} AS lv\n ON {$cls_join_on}\n WHERE\n usr.company_id = {$company_id}\n AND {$cls_date}\n GROUP BY\n subject\n ORDER BY\n count DESC\n "); } /*if ($subject == 'love_within_team') { $teams = teams($company_id); foreach ($records as $i => $record) { $records[$i][1] /= $teams[$record[0]]; } }*/ }
function browse_table($select, $base_href = "") { $rows = fetch_rows($select); if (!count($rows)) { return "<p><i>None</i></p>\n"; } $retval = "<table class='browse-table'>\n<tr>"; foreach (array_slice(array_keys($rows[0]), 1) as $key) { $retval .= "<th>" . htmlentities($key) . "</th>"; } $retval .= "</tr>\n"; $href = ""; foreach ($rows as $row) { $row_num++; $retval .= "<tr>"; $col_num = 0; $col1 = ""; foreach ($row as $key => $value) { $col_num++; $out_value = htmlentities($value); $data = ""; if ($col_num == 1) { $href = $out_value && $base_href ? href($base_href . $out_value) : ""; $col1 = $out_value; continue; } elseif ($col_num == 2 && $href) { $out_value = "<a href='{$href}'>{$out_value}</a>"; } elseif ($col_num == 2) { $data = " data-col1='{$col1}'"; } $retval .= "<td{$data}>{$out_value}</td>"; } $retval .= "</tr>\n"; } $retval .= "</table>\n"; return $retval; }