public static function get_finance_summary($week_start, $week_end, $multiplyer = 1, $row_limit = 7)
{
$cache_key = 'finance_sum_' . md5(module_security::get_loggedin_id() . '_' . serialize(func_get_args()));
$cache_timeout = module_config::c('cache_objects', 60);
if ($cached_item = module_cache::get('finance', $cache_key)) {
return $cached_item;
}
$base_href = module_finance::link_generate(false, array('full' => false, 'page' => 'dashboard_popup', 'arguments' => array('display_mode' => 'ajax')), array('foo'));
$base_href .= '&';
/*$base_href .= (strpos($base_href,'?')!==false) ? '&' : '?';
$base_href .= 'display_mode=ajax&';
$base_href .= 'home_page_stats=true&';*/
// init structure:
if ($multiplyer > 1) {
$row_limit++;
}
for ($x = 0; $x < $row_limit; $x++) {
//$time = strtotime("+$x days",strtotime($week_start));
$time = strtotime("+" . $x * $multiplyer . " days", strtotime($week_start));
$data[date("Ymd", $time)] = array("day" => $time, "hours" => 0, "amount" => 0, "amount_invoiced" => 0, "amount_paid" => 0, "amount_spent" => 0);
if (class_exists('module_envato', false)) {
$data[date("Ymd", $time)]['envato_earnings'] = 0;
}
}
$data['total'] = array('day' => _l('Totals:'), 'week' => _l('Totals:'), 'hours' => 0, 'amount' => 0, 'amount_invoiced' => 0, 'amount_paid' => 0, 'amount_spent' => 0);
if (class_exists('module_envato', false)) {
$data['total']['envato_earnings'] = 0;
}
if (class_exists('module_job', false)) {
module_debug::log(array('title' => 'Finance Dashboard Job', 'data' => ''));
// find all task LOGS completed within these dayes
$sql = "SELECT t.task_id, tl.date_created, t.hours AS task_hours, t.amount, tl.hours AS hours_logged, p.job_id, p.hourly_rate, t.date_done ";
// $sql .= " FROM `"._DB_PREFIX."task_log` tl ";
// $sql .= " LEFT JOIN `"._DB_PREFIX."task` t ON tl.task_id = t.task_id ";
$sql .= " FROM `" . _DB_PREFIX . "task` t";
$sql .= " LEFT JOIN `" . _DB_PREFIX . "task_log` tl ON t.task_id = tl.task_id ";
$sql .= " LEFT JOIN `" . _DB_PREFIX . "job` p ON t.job_id = p.job_id";
$sql .= " WHERE ( (tl.date_created >= '{$week_start}' AND tl.date_created < '{$week_end}') OR (t.fully_completed = 1 AND t.date_done >= '{$week_start}' AND t.date_done < '{$week_end}') )";
$sql .= " AND t.job_id IN ( ";
$valid_job_ids = module_job::get_valid_job_ids();
if (count($valid_job_ids)) {
foreach ($valid_job_ids as $valid_job_id) {
$sql .= (int) $valid_job_id['job_id'] . ", ";
}
$sql = rtrim($sql, ', ');
} else {
$sql .= ' NULL ';
}
$sql .= " ) ";
// echo $sql;
$tasks = query($sql);
$logged_tasks = array();
while ($r = mysql_fetch_assoc($tasks)) {
if (!$r['date_created']) {
$r['date_created'] = $r['date_done'];
}
if ($multiplyer > 1) {
$week_day = date('w', strtotime($r['date_created'])) - 1;
$r['date_created'] = date('Y-m-d', strtotime('-' . $week_day . ' days', strtotime($r['date_created'])));
}
$key = date("Ymd", strtotime($r['date_created']));
if (!isset($data[$key])) {
// for some reason we're getting results here that shouldn't be in the list
// for now we just skip these results until I figure out why (only had 1 guy report this error, maybe misconfig)
continue;
}
// copied from dashboard_popup_hours_logged.php
// needed get_tasks call to do the _JOB_TASK_ACCESS_ASSIGNED_ONLY permission check
$jobtasks = module_job::get_tasks($r['job_id']);
$task = isset($jobtasks[$r['task_id']]) ? $jobtasks[$r['task_id']] : false;
if (!$task) {
continue;
}
if (!isset($task['manual_task_type']) || $task['manual_task_type'] < 0) {
$task['manual_task_type'] = $task['default_task_type'];
}
if (isset($r['hours_logged']) && $r['hours_logged'] > 0) {
if ($r['hours_logged'] == $task['completed']) {
// this listing is the only logged hours for this task.
if ($task['fully_completed']) {
// task complete, we show the final amount and hours.
if ($task['amount'] > 0) {
if ($task['manual_task_type'] == _TASK_TYPE_QTY_AMOUNT) {
$display_amount = $task['amount'] * $task['hours'];
} else {
$display_amount = $task['amount'];
}
} else {
$display_amount = $r['task_hours'] * $r['hourly_rate'];
}
} else {
// task isn't fully completed yet, just use hourly rate for now.
$display_amount = $r['hours_logged'] * $r['hourly_rate'];
}
} else {
// this is part of a bigger log of hours for this single task.
$display_amount = $r['hours_logged'] * $r['hourly_rate'];
}
$hours_logged = $r['task_hours'] > 0 ? $r['hours_logged'] : 0;
} else {
// there are no logged hours for this particular task, but it is set to completed.
// we just assume it is completed on this day.
if ($task['amount'] > 0) {
if ($task['manual_task_type'] == _TASK_TYPE_QTY_AMOUNT) {
$display_amount = $task['amount'] * $task['hours'];
} else {
$display_amount = $task['amount'];
}
} else {
$display_amount = $r['task_hours'] * $r['hourly_rate'];
}
$hours_logged = $task['hours'];
}
$data[$key]['amount'] += $display_amount;
$data['total']['amount'] += $display_amount;
$data[$key]['hours'] += $hours_logged;
$data['total']['hours'] += $hours_logged;
/*$hourly_rate = $r['hourly_rate'];
if($hours_logged > 0 && $r['amount'] > 0 && $hourly_rate > 0){
// there is a custom amount assigned to thsi task.
// only calculate this amount if the full hours is complete.
$hourly_rate = $r['amount'] / $r['task_hours'];
}
if($hours_logged > 0 && $hourly_rate > 0){
$data[$key]['amount'] += ($hours_logged * $hourly_rate);
$data['total']['amount'] += ($hours_logged * $hourly_rate);
}*/
}
}
module_debug::log(array('title' => 'Finance Dashboard Invoices', 'data' => ''));
// find invoices sent this week.
$sql = "SELECT i.* ";
$sql .= " FROM `" . _DB_PREFIX . "invoice` i ";
$sql .= " LEFT JOIN `" . _DB_PREFIX . "invoice_item` ii ON i.invoice_id = ii.invoice_id ";
if (class_exists('module_job', false)) {
$sql .= " LEFT JOIN `" . _DB_PREFIX . "task` t ON ii.task_id = t.task_id ";
$sql .= " LEFT JOIN `" . _DB_PREFIX . "job` p ON t.job_id = p.job_id ";
}
$sql .= " WHERE (i.date_create >= '{$week_start}' AND i.date_create <= '{$week_end}')";
$sql .= " GROUP BY i.invoice_id";
// todo - sql in here to limit what they can see.
$invoices = query($sql);
// group invoices into days of the week.
while ($invoice_data = mysql_fetch_assoc($invoices)) {
//$invoice_data = module_invoice::get_invoice($i['invoice_id']);
if ($invoice_data) {
if ($multiplyer > 1) {
$week_day = date('w', strtotime($invoice_data['date_create'])) - 1;
$invoice_data['date_create'] = date('Y-m-d', strtotime('-' . $week_day . ' days', strtotime($invoice_data['date_create'])));
}
$key = date("Ymd", strtotime($invoice_data['date_create']));
if (!isset($data[$key])) {
// for some reason we're getting results here that shouldn't be in the list
// for now we just skip these results until I figure out why (only had 1 guy report this error, maybe misconfig)
continue;
}
if (isset($data[$key])) {
$data[$key]['amount_invoiced'] += $invoice_data['c_total_amount'];
$data['total']['amount_invoiced'] += $invoice_data['c_total_amount'];
}
}
}
module_debug::log(array('title' => 'Finance Dashboard Finances', 'data' => ''));
// find all payments made this week.
// we also have to search for entries in the new "finance" table and make sure we dont double up here.
$finance_records = module_finance::get_finances(array('date_from' => $week_start, 'date_to' => $week_end));
foreach ($finance_records as $finance_record) {
if (isset($finance_record['payment_type']) && ($finance_record['payment_type'] == _INVOICE_PAYMENT_TYPE_OVERPAYMENT_CREDIT || $finance_record['payment_type'] == _INVOICE_PAYMENT_TYPE_CREDIT)) {
// CODE COPIED FROM FINANCE_LIST.PHP
// dont add these ones to the totals on the dashboard
continue;
}
if ($finance_record['credit'] > 0) {
if ($multiplyer > 1) {
$week_day = date('w', strtotime($finance_record['transaction_date'])) - 1;
$finance_record['transaction_date'] = date('Y-m-d', strtotime('-' . $week_day . ' days', strtotime($finance_record['transaction_date'])));
}
$key = date("Ymd", strtotime($finance_record['transaction_date']));
if (isset($data[$key])) {
$data[$key]['amount_paid'] += $finance_record['amount'];
$data['total']['amount_paid'] += $finance_record['amount'];
}
}
if ($finance_record['debit'] > 0) {
if ($multiplyer > 1) {
$week_day = date('w', strtotime($finance_record['transaction_date'])) - 1;
$finance_record['transaction_date'] = date('Y-m-d', strtotime('-' . $week_day . ' days', strtotime($finance_record['transaction_date'])));
}
$key = date("Ymd", strtotime($finance_record['transaction_date']));
if (isset($data[$key])) {
$data[$key]['amount_spent'] += $finance_record['amount'];
$data['total']['amount_spent'] += $finance_record['amount'];
}
}
}
module_debug::log(array('title' => 'Finance Dashboard DONE!', 'data' => ''));
/*$sql = "SELECT p.* ";
$sql .= " FROM `"._DB_PREFIX."invoice_payment` p ";
$sql .= " WHERE (p.date_paid >= '$week_start' AND p.date_paid <= '$week_end')";
// todo - sql in here to limit what they can see.
$payments = query($sql);
// group invoices into days of the week.
while($payment = mysql_fetch_assoc($payments)){
//$invoice_data = module_invoice::get_invoice($i['invoice_id']);
if($multiplyer > 1){
$week_day = date('w',strtotime($payment['date_paid'])) - 1;
$payment['date_paid'] = date('Y-m-d',strtotime('-'.$week_day.' days',strtotime($payment['date_paid'])));
}
$key = date("Ymd",strtotime($payment['date_paid']));
if(isset($data[$key])){
$data[$key]['amount_paid'] += $payment['amount'];
$data['total']['amount_paid'] += $payment['amount'];
}
}*/
if (class_exists('module_envato', false)) {
$envato_currency = "USD";
$envato = new envato_api();
$local_currency = $envato->read_setting("local_currency", "AUD");
$currency_convert_multiplier = $envato->currency_convert($envato_currency, $local_currency);
// find summary of earnings between these dates in the envato statement.
$week_start_time = strtotime($week_start);
$week_end_time = strtotime($week_end);
$sql = "SELECT * FROM `" . _DB_PREFIX . "envato_statement` s WHERE `time` >= '{$week_start_time}' AND `time` <= {$week_end_time}";
$sql .= " AND ( `type` = 'sale' OR `type` = 'referral_cut' )";
foreach (qa($sql) as $sale) {
$sale_time = $sale['time'];
if ($multiplyer > 1) {
$week_day = date('w', $sale_time) - 1;
$sale_time = strtotime('-' . $week_day . ' days', $sale_time);
}
$key = date("Ymd", $sale_time);
if (!isset($data[$key])) {
continue;
}
$data[$key]['envato_earnings'] += round($currency_convert_multiplier * $sale['earnt'], 2);
$data['total']['envato_earnings'] += round($currency_convert_multiplier * $sale['earnt'], 2);
/*if($sale['type']=='sale'){
$sales_count++;
}
$sales_amount+= $sale['earnt'];*/
}
}
if ($multiplyer > 1) {
// dont want totals on previous weeks listing
unset($data['total']);
}
foreach ($data as $data_id => $row) {
//$row['amount'] = dollar($row['amount']);
$row['chart_amount'] = $row['amount'];
$row['amount'] = currency((int) $row['amount']);
$row['chart_amount_invoiced'] = $row['amount_invoiced'];
$row['amount_invoiced'] = currency((int) $row['amount_invoiced']);
$row['chart_amount_paid'] = $row['amount_paid'];
$row['amount_paid'] = currency((int) $row['amount_paid']);
$row['chart_amount_spent'] = $row['amount_spent'];
$row['amount_spent'] = currency((int) $row['amount_spent']);
if (class_exists('module_envato', false)) {
$row['chart_envato_earnings'] = $row['envato_earnings'];
$row['envato_earnings'] = currency((int) $row['envato_earnings']);
}
// combine together
$row['chart_hours'] = $row['hours'];
$row['hours'] = sprintf('%s (%s)', $row['hours'], $row['amount']);
if (is_numeric($row['day'])) {
$time = $row['day'];
$date = date('Y-m-d', $time);
$row['date'] = $date;
if ($multiplyer > 1) {
$date .= '|' . date('Y-m-d', strtotime('+' . $multiplyer . ' days', $time));
}
//$row['hours'] = '<a href="'.$base_href.'w=hours&date='.$date.'" class="summary_popup">'. _l('%s hours',$row['hours']) . '</a>';
$row['hours_link'] = '<a href="' . $base_href . 'w=hours&date=' . $date . '" class="summary_popup">' . $row['hours'] . '</a>';
$row['amount_link'] = '<a href="' . $base_href . 'w=hours&date=' . $date . '" class="summary_popup">' . $row['amount'] . '</a>';
$row['amount_invoiced_link'] = '<a href="' . $base_href . 'w=amount_invoiced&date=' . $date . '" class="summary_popup">' . $row['amount_invoiced'] . '</a>';
$row['amount_paid_link'] = '<a href="' . $base_href . 'w=amount_paid&date=' . $date . '" class="summary_popup">' . $row['amount_paid'] . '</a>';
$row['amount_spent_link'] = '<a href="' . $base_href . 'w=amount_spent&date=' . $date . '" class="summary_popup">' . $row['amount_spent'] . '</a>';
$row['day'] = _l(date('D', $time)) . ' ' . date('j', $time) . _l(date('S', $time));
$row['week'] = _l(date('M', $time)) . ' ' . date('j', $time) . _l(date('S', $time));
// if it's today.
if ($time == strtotime(date("Y-m-d"))) {
$row['highlight'] = true;
}
} else {
}
$data[$data_id] = $row;
}
module_cache::put('finance', $cache_key, $data, $cache_timeout);
return $data;
}
}
$sql = "SELECT tl.date_created, t.amount, tl.hours AS hours_logged, p.hourly_rate ";
$sql .= ", t.description AS task_name ";
$sql .= ", p.job_id ";
$sql .= ", p.website_id ";
$sql .= ", t.task_id ";
$sql .= ", t.hours ";
$sql .= " FROM `" . _DB_PREFIX . "task` t";
$sql .= " LEFT JOIN `" . _DB_PREFIX . "task_log` tl ON t.task_id = tl.task_id ";
//$sql .= " FROM `"._DB_PREFIX."task_log` tl ";
//$sql .= " LEFT JOIN `"._DB_PREFIX."task` t ON tl.task_id = t.task_id ";
$sql .= " LEFT JOIN `" . _DB_PREFIX . "job` p ON t.job_id = p.job_id";
//$sql .= " WHERE tl.date_created >= '$start_date' AND tl.date_created < '$end_date'";
$sql .= " WHERE ( (tl.date_created >= '{$start_date}' AND tl.date_created < '{$end_date}') OR (t.fully_completed = 1 AND t.date_done >= '{$start_date}' AND t.date_done < '{$end_date}') )";
$sql .= " AND t.job_id IN ( ";
$valid_job_ids = module_job::get_valid_job_ids();
if (count($valid_job_ids)) {
foreach ($valid_job_ids as $valid_job_id) {
$sql .= (int) $valid_job_id['job_id'] . ", ";
}
$sql = rtrim($sql, ', ');
} else {
$sql .= ' NULL ';
}
$sql .= " ) ";
// order by ?
$res = qa($sql);
$title = _l('Hours Logged');
print_heading("{$title} for " . print_date($start_date) . $end_date_str);
$total = 0;
?>