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