/**
* returns list of time summary attached to event ID's within specific timespace as array
*
* @param integer $in start time in unix seconds
* @param integer $out end time in unix seconds
* @param integer $user filter for only this ID of auser
* @param integer $customer filter for only this ID of a customer
* @param integer $project filter for only this ID of a project
* @global array $kga kimai-global-array
* @return array
* @author sl
*/
function get_arr_time_evt($in, $out, $users = null, $customers = null, $projects = null, $events = null)
{
global $kga;
global $conn;
$in = MySQL::SQLValue($in, MySQL::SQLVALUE_NUMBER);
$out = MySQL::SQLValue($out, MySQL::SQLVALUE_NUMBER);
$p = $kga['server_prefix'];
$whereClauses = zef_whereClausesFromFilters($users, $customers, $projects, $events);
$whereClauses[] = "{$p}evt.evt_trash = 0";
if ($in) {
$whereClauses[] = "zef_out > {$in}";
}
if ($out) {
$whereClauses[] = "zef_in < {$out}";
}
$query = "SELECT zef_in, zef_out,zef_evtID, (zef_out - zef_in) / 3600 * zef_rate AS costs\n FROM {$p}zef \n Left Join {$p}evt ON zef_evtID = evt_ID\n Left Join {$p}pct ON zef_pctID = pct_ID\n Left Join {$p}knd ON pct_kndID = knd_ID " . (count($whereClauses) > 0 ? " WHERE " : " ") . implode(" AND ", $whereClauses);
$result = $conn->Query($query);
if (!$result) {
return array();
}
$rows = $conn->RecordsArray(MYSQL_ASSOC);
if (!$rows) {
return array();
}
$arr = array();
$zef_in = 0;
$zef_out = 0;
foreach ($rows as $row) {
if ($row['zef_in'] <= $in && $row['zef_out'] < $out) {
$zef_in = $in;
$zef_out = $row['zef_out'];
} else {
if ($row['zef_in'] <= $in && $row['zef_out'] >= $out) {
$zef_in = $in;
$zef_out = $out;
} else {
if ($row['zef_in'] > $in && $row['zef_out'] < $out) {
$zef_in = $row['zef_in'];
$zef_out = $row['zef_out'];
} else {
if ($row['zef_in'] > $in && $row['zef_out'] >= $out) {
$zef_in = $row['zef_in'];
$zef_out = $out;
}
}
}
}
if (isset($arr[$row['zef_evtID']])) {
$arr[$row['zef_evtID']]['time'] += (int) ($zef_out - $zef_in);
$arr[$row['zef_evtID']]['costs'] += (double) $row['costs'];
} else {
$arr[$row['zef_evtID']]['time'] = (int) ($zef_out - $zef_in);
$arr[$row['zef_evtID']]['costs'] = (double) $row['costs'];
}
}
return $arr;
}
/**
* returns list of time summary attached to event ID's within specific timespace as array
*
* @param integer $in start time in unix seconds
* @param integer $out end time in unix seconds
* @param integer $user filter for only this ID of auser
* @param integer $customer filter for only this ID of a customer
* @param integer $project filter for only this ID of a project
* @global array $kga kimai-global-array
* @return array
* @author sl
*/
function get_arr_time_evt($in, $out, $users = null, $customers = null, $projects = null, $events = null)
{
global $kga, $pdo_conn;
$p = $kga['server_prefix'];
$whereClauses = zef_whereClausesFromFilters($users, $customers, $projects, $events);
$whereClauses[] = "{$p}evt.evt_trash = 0";
if ($in) {
$whereClauses[] = "zef_out > {$in}";
}
if ($out) {
$whereClauses[] = "zef_in < {$out}";
}
$pdo_query = $pdo_conn->prepare("SELECT zef_in,zef_out,zef_evtID, (zef_out - zef_in) / 3600 * zef_rate AS costs\n FROM {$p}zef \n Left Join {$p}evt ON zef_evtID = evt_ID\n Left Join {$p}pct ON zef_pctID = pct_ID\n Left Join {$p}knd ON pct_kndID = knd_ID " . (count($whereClauses) > 0 ? " WHERE " : " ") . implode(" AND ", $whereClauses));
$pdo_query->execute();
$arr = array();
$zef_in = 0;
$zef_out = 0;
while ($row = $pdo_query->fetch(PDO::FETCH_ASSOC)) {
if ($row['zef_in'] <= $in && $row['zef_out'] < $out) {
$zef_in = $in;
$zef_out = $row['zef_out'];
} else {
if ($row['zef_in'] <= $in && $row['zef_out'] >= $out) {
$zef_in = $in;
$zef_out = $out;
} else {
if ($row['zef_in'] > $in && $row['zef_out'] < $out) {
$zef_in = $row['zef_in'];
$zef_out = $row['zef_out'];
} else {
if ($row['zef_in'] > $in && $row['zef_out'] >= $out) {
$zef_in = $row['zef_in'];
$zef_out = $out;
}
}
}
}
if (isset($arr[$row['zef_evtID']])) {
$arr[$row['zef_evtID']]['time'] += (int) ($zef_out - $zef_in);
$arr[$row['zef_evtID']]['costs'] += (int) $row['costs'];
} else {
$arr[$row['zef_evtID']]['time'] = (int) ($zef_out - $zef_in);
$arr[$row['zef_evtID']]['costs'] = (int) $row['costs'];
}
}
return $arr;
}
