<?php

defined('BASEPATH') or exit('No direct script access allowed');

// 5.6 mysql version don't have the ANY_VALUE function implemented.
$v = $this->ci->db->query('SELECT VERSION() as version')->row();

$roundTimesheets = get_option('round_off_task_timer_option') != 0;

$isMariaDB = false;

// Check if MariaDB or MySQL
if ($v) {
    $version = $v->version;

    if (stripos($version, 'mariadb') !== false) {
        $isMariaDB = true;
    } else {
        if (version_compare($version, '5.7', '>=')) {
            $supportsAnyValue = true;
        }
    }
}

$additionalSelect = array_filter([
    db_prefix() . 'taskstimers.id',
    'task_id',
    'rel_type',
    'rel_id',
    $roundTimesheets ? 'start_time' : '',
    $roundTimesheets ? 'end_time' : '',
    'billed',
    'status',
]);

$staffIdSelect = '';
if (! $isMariaDB && isset($supportsAnyValue) && $supportsAnyValue) {
    $staffIdSelect = 'ANY_VALUE(staff_id) as staff_id';

    foreach ($additionalSelect as $key => $column) {
        if ($key !== 0) {
            $additionalSelect[$key] = 'ANY_VALUE(' . $column . ') as ' . $column;
        } else {
            // causing errors for ambigious column
            $additionalSelect[$key] = 'ANY_VALUE(' . $column . ') as id';
        }
    }

    $aColumns = array_values(array_filter([
        'ANY_VALUE(name) as name',
        'ANY_VALUE((SELECT GROUP_CONCAT(name SEPARATOR ",") FROM ' . db_prefix() . 'taggables JOIN ' . db_prefix() . 'tags ON ' . db_prefix() . 'taggables.tag_id = ' . db_prefix() . 'tags.id WHERE rel_id = ' . db_prefix() . 'taskstimers.id and rel_type="timesheet" ORDER by tag_order ASC)) as tags',
        ! $roundTimesheets ? 'ANY_VALUE(start_time) as start_time' : '',
        ! $roundTimesheets ? 'ANY_VALUE(end_time) as end_time' : '',
        'ANY_VALUE(note) as note',
        'ANY_VALUE(' . tasks_rel_name_select_query() . ') as rel_name',
        'ANY_VALUE(end_time - start_time) as time_h',
        'ANY_VALUE(end_time - start_time) as time_d',
    ]));
} else {
    $staffIdSelect = 'staff_id';

    $aColumns = array_values(array_filter([
        'name as name',
        '(SELECT GROUP_CONCAT(name SEPARATOR ",") FROM ' . db_prefix() . 'taggables JOIN ' . db_prefix() . 'tags ON ' . db_prefix() . 'taggables.tag_id = ' . db_prefix() . 'tags.id WHERE rel_id = ' . db_prefix() . 'taskstimers.id and rel_type="timesheet" ORDER by tag_order ASC) as tags',
        ! $roundTimesheets ? 'start_time' : '',
        ! $roundTimesheets ? 'end_time' : '',
        'note as note',
        tasks_rel_name_select_query() . ' as rel_name',
        'end_time - start_time as time_h',
        'end_time - start_time as time_d',
    ]));
}

$time_h_column = 6;
$time_d_column = 7;

if ($view_all == true) {
    array_unshift($aColumns, $staffIdSelect);
    $time_h_column++;
    $time_d_column++;
}

if ($roundTimesheets) {
    $time_h_column = $time_h_column - 2;
    $time_d_column = $time_d_column - 2;
}

if ($this->ci->input->post('group_by_task')) {
    if ($v && strpos($v->version, '5.7') !== false) {
        $aColumns[$time_h_column] = 'ANY_VALUE((SUM(end_time - start_time))) as time_h';
        $aColumns[$time_d_column] = 'ANY_VALUE((SUM(end_time - start_time))) as time_d';
    } else {
        $aColumns[$time_h_column] = 'SUM(end_time - start_time) as time_h';
        $aColumns[$time_d_column] = 'SUM(end_time - start_time) as time_d';
    }
}

$sIndexColumn = 'id';
$sTable       = db_prefix() . 'taskstimers';

$join = [
    'LEFT JOIN ' . db_prefix() . 'tasks ON ' . db_prefix() . 'tasks.id = ' . db_prefix() . 'taskstimers.task_id',
];

$where = [];

$staff_id = false;

if ($this->ci->input->post('staff_id')) {
    $staff_id = $this->ci->input->post('staff_id');
} elseif ($view_all == false) {
    $staff_id = get_staff_user_id();
}

if ($staff_id != false) {
    $where = [
        'AND staff_id=' . $this->ci->db->escape_str($staff_id),
    ];
}

$project_ids = $this->ci->input->post('project_id');

if ($project_ids && is_array($project_ids)) {
    $project_ids = array_filter($project_ids, function ($value) {
        return $value !== '';
    });

    if (count($project_ids) > 0) {
        $project_ids = implode(',', array_map(function ($project_id) {
            return get_instance()->db->escape_str($project_id);
        }, $project_ids));
        array_push($where, 'AND task_id IN (SELECT id FROM ' . db_prefix() . 'tasks WHERE rel_type = "project" AND rel_id  IN (' . $project_ids . '))');
    }
}

if ($this->ci->input->post('clientid') && ! $this->ci->input->post('project_id')) {
    $customer_id = $this->ci->db->escape_str($this->ci->input->post('clientid'));

    array_push($where, 'AND (
(rel_id IN (SELECT id FROM ' . db_prefix() . 'invoices WHERE clientid=' . $customer_id . ') AND rel_type="invoice")
OR
(rel_id IN (SELECT id FROM ' . db_prefix() . 'estimates WHERE clientid=' . $customer_id . ') AND rel_type="estimate")
OR
(rel_id IN (SELECT id FROM ' . db_prefix() . 'contracts WHERE client=' . $customer_id . ') AND rel_type="contract")
OR
( rel_id IN (SELECT ticketid FROM ' . db_prefix() . 'tickets WHERE userid=' . $customer_id . ') AND rel_type="ticket")
OR
(rel_id IN (SELECT id FROM ' . db_prefix() . 'expenses WHERE clientid=' . $customer_id . ') AND rel_type="expense")
OR
(rel_id IN (SELECT id FROM ' . db_prefix() . 'proposals WHERE rel_id=' . $customer_id . ' AND rel_type="customer") AND rel_type="proposal")
OR
(rel_id IN (SELECT userid FROM ' . db_prefix() . 'clients WHERE userid=' . $customer_id . ') AND rel_type="customer")
OR
(rel_id IN (SELECT id FROM ' . db_prefix() . 'projects WHERE clientid=' . $customer_id . ') AND rel_type="project")
)');
}

array_push($where, 'AND task_id != 0');

$filter = $this->ci->input->post('range');
if ($filter != 'period') {
    if ($filter == 'today') {
        $beginOfDay = strtotime('midnight');
        $endOfDay   = strtotime('tomorrow', $beginOfDay) - 1;
        array_push($where, ' AND start_time BETWEEN ' . $beginOfDay . ' AND ' . $endOfDay);
    } elseif ($filter == 'this_month') {
        $beginThisMonth = date('Y-m-01');
        $endThisMonth   = date('Y-m-t 23:59:59');
        array_push($where, ' AND start_time BETWEEN ' . strtotime($beginThisMonth) . ' AND ' . strtotime($endThisMonth));
    } elseif ($filter == 'last_month') {
        $beginLastMonth = date('Y-m-01', strtotime('-1 MONTH'));
        $endLastMonth   = date('Y-m-t 23:59:59', strtotime('-1 MONTH'));
        array_push($where, ' AND start_time BETWEEN ' . strtotime($beginLastMonth) . ' AND ' . strtotime($endLastMonth));
    } elseif ($filter == 'this_week') {
        $beginThisWeek = date('Y-m-d', strtotime('monday this week'));
        $endThisWeek   = date('Y-m-d 23:59:59', strtotime('sunday this week'));
        array_push($where, ' AND start_time BETWEEN ' . strtotime($beginThisWeek) . ' AND ' . strtotime($endThisWeek));
    } elseif ($filter == 'last_week') {
        $beginLastWeek = date('Y-m-d', strtotime('monday last week'));
        $endLastWeek   = date('Y-m-d 23:59:59', strtotime('sunday last week'));
        array_push($where, ' AND start_time BETWEEN ' . strtotime($beginLastWeek) . ' AND ' . strtotime($endLastWeek));
    }
} else {
    $start_date = to_sql_date($this->ci->input->post('period-from'));
    $end_date   = to_sql_date($this->ci->input->post('period-to'));
    array_push($where, ' AND start_time BETWEEN ' . strtotime($start_date . ' 00:00:00') . ' AND ' . strtotime($end_date . ' 23:59:00'));
}

$result = data_tables_init(
    $aColumns,
    $sIndexColumn,
    $sTable,
    $join,
    $where,
    $additionalSelect,
    ($this->ci->input->post('group_by_task') ? 'GROUP BY task_id' : '')
);

$output  = $result['output'];
$rResult = $result['rResult'];

$footer_data['total_logged_time_h'] = 0;
$footer_data['total_logged_time_d'] = 0;

$footer_data['chart']           = [];
$footer_data['chart']['labels'] = [];
$footer_data['chart']['data']   = [];

$temp_weekdays_data           = [];
$temp_months_data             = [];
$chart_type                   = 'today';
$chart_type_month_from_filter = false;
$weekDay                      = date('w', strtotime(date('Y-m-d H:i:s')));
if ($filter == 'today') {
    $footer_data['chart']['labels'] = [_l('today')];
} elseif ($filter == 'this_week' || $filter == 'last_week') {
    foreach (get_weekdays() as $day) {
        array_push($footer_data['chart']['labels'], $day);
    }
    $i = 0;

    foreach (get_weekdays_original() as $day) {
        if ($weekDay != '0') {
            $footer_data['chart']['labels'][$i] = date('d', strtotime($day . ' ' . str_replace('_', ' ', $filter))) . ' - ' . $footer_data['chart']['labels'][$i];
        } else {
            if ($filter == 'this_week') {
                $strtotime = 'last ' . $day;
                if ($day == 'Sunday') {
                    $strtotime = 'sunday this week';
                }
                $footer_data['chart']['labels'][$i] = date('d', strtotime($strtotime)) . ' - ' . $footer_data['chart']['labels'][$i];
            } else {
                $strtotime                          = $day . ' last week';
                $footer_data['chart']['labels'][$i] = date('d', strtotime($strtotime)) . ' - ' . $footer_data['chart']['labels'][$i];
            }
        }
        $i++;
    }

    $chart_type = 'week';
} elseif ($filter == 'this_month' || $filter == 'last_month') {
    $month      = ($filter == 'this_month') ? date('m') : date('m', strtotime('first day last month'));
    $month_year = ($filter == 'this_month') ? date('Y') : date('Y', strtotime('first day last month'));

    for ($d = 1; $d <= 31; $d++) {
        $time = mktime(12, 0, 0, $month, $d, $month_year);
        if (date('m', $time) == $month) {
            array_push($footer_data['chart']['labels'], date('Y-m-d', $time));
        }
    }
    $chart_type = 'month';
} else {
    $_start_time = new DateTime(date('Y-m-d', strtotime($start_date)));
    $_end_time   = new DateTime(date('Y-m-d', strtotime($end_date)));

    $chart_type  = 'weeks_split';
    $weeks       = get_weekdays_between_dates($_start_time, $_end_time);
    $total_weeks = count($weeks);

    for ($i = 1; $i <= $total_weeks; $i++) {
        array_push($footer_data['chart']['labels'], split_weeks_chart_label($weeks, $i));
    }
}

$chartWhere = implode(' ', $where);
$chartWhere = ltrim($chartWhere, 'AND ');

$chartData = $this->ci->db->query('SELECT end_time - start_time logged_time_h,
end_time - start_time logged_time_d,start_time,end_time FROM ' . db_prefix() . 'taskstimers LEFT JOIN ' . db_prefix() . 'tasks ON ' . db_prefix() . 'tasks.id = ' . db_prefix() . 'taskstimers.task_id WHERE ' . trim($chartWhere))->result_array();

foreach ($chartData as $timer) {
    if ($timer['logged_time_h'] == null) {
        $footer_data['total_logged_time_h'] += task_timer_round((time() - $timer['start_time']));
    } else {
        $footer_data['total_logged_time_h'] += task_timer_round($timer['logged_time_h']);
    }

    if ($timer['logged_time_d'] == null) {
        $total_logged_time_d = task_timer_round(time() - $timer['start_time']);
    } else {
        $total_logged_time_d = task_timer_round($timer['logged_time_d']);
    }
    if ($chart_type == 'today') {
        array_push($footer_data['chart']['data'], $total_logged_time_d);
    } elseif ($chart_type == 'week') {
        $weekday = date('N', $timer['start_time']);
        if (! isset($temp_weekdays_data[$weekday])) {
            $temp_weekdays_data[$weekday] = 0;
        }
        $temp_weekdays_data[$weekday] += $total_logged_time_d;
    } elseif ($chart_type == 'month') {
        $month = intval(date('d', $timer['start_time']));
        if (! isset($temp_months_data[$month])) {
            $temp_months_data[$month] = 0;
        }

        $temp_months_data[$month] += $total_logged_time_d;
    } elseif ($chart_type == 'weeks_split') {
        $w = 1;

        foreach ($weeks as $week) {
            $start_time_date = date('Y-m-d', $timer['start_time']);

            if (! isset($weeks[$w]['total'])) {
                $weeks[$w]['total'] = 0;
            }
            if (in_array($start_time_date, $week)) {
                $weeks[$w]['total'] += $total_logged_time_d;
            }
            $w++;
        }
    }
    $footer_data['total_logged_time_d'] += $total_logged_time_d;
}

foreach ($rResult as $aRow) {
    $row = [];

    if ($view_all === true) {
        $row[] = '<a href="' . admin_url('staff/member/' . $aRow['staff_id']) . '" target="_blank">' . e(get_staff_full_name($aRow['staff_id'])) . '</a>';
    }

    $taskName = '<a href="' . admin_url('tasks/view/' . $aRow['task_id']) . '" onclick="init_task_modal(' . $aRow['task_id'] . '); return false;">' . e($aRow['name']) . '</a>';

    $status = get_task_status_by_id($aRow['status']);

    $taskName .= '<br /><span class="hidden"> - </span><span class="label" style="color:' . $status['color'] . ';border:1px solid ' . adjust_hex_brightness($status['color'], 0.4) . ';background: ' . adjust_hex_brightness($status['color'], 0.04) . ';" task-status-table="' . $aRow['status'] . '">' . e($status['name']) . '</span>';

    if (! $this->ci->input->post('group_by_task') && (! $aRow['end_time'] && is_admin() && $aRow['billed'] == 0)) {
        $taskName .= '<br /><a href="#"
        data-toggle="popover"
        data-placement="bottom"
        data-html="true"
        data-trigger="manual"
        data-title="' . _l('note') . "\"
        data-content='" . render_textarea('timesheet_note') . '
        <button type="button"
        onclick="timer_action(this, ' . $aRow['task_id'] . ', ' . $aRow['id'] . ', 1);"
        class="btn btn-primary btn-sm">' . _l('save')
        . "</button>'
        class=\"text-danger\"
        onclick=\"return false;\">
        <i class=\"fa-regular fa-clock\"></i> " . _l('task_stop_timer') . '
        </a>';
    }

    $row[] = $taskName;

    $row[] = render_tags($aRow['tags']);

    if (! $roundTimesheets) {
        $row[] = e(_dt($aRow['start_time'], true));
        $row[] = e(($aRow['end_time'] ? _dt($aRow['end_time'], true) : ''));
    }

    $row[] = process_text_content_for_display($aRow['note']);

    if ($aRow['rel_name']) {
        $relName = task_rel_name($aRow['rel_name'], $aRow['rel_id'], $aRow['rel_type']);
        $link    = task_rel_link($aRow['rel_id'], $aRow['rel_type']);
        $row[]   = '<a href="' . $link . '">' . e($relName) . '</a>';
    } else {
        $row[] = '';
    }

    $total_logged_time = 0;
    if ($aRow['time_h'] == null) {
        $total_logged_time = time() - $aRow['start_time'];
    } else {
        $total_logged_time = $aRow['time_h'];
    }
    $row[] = e(seconds_to_time_format(task_timer_round($total_logged_time)));

    $total_logged_time = 0;
    if ($aRow['time_d'] == null) {
        $total_logged_time = time() - $aRow['start_time'];
    } else {
        $total_logged_time = $aRow['time_d'];
    }
    $row[] = e(sec2qty(task_timer_round($total_logged_time)));

    $output['aaData'][] = $row;
}

if ($chart_type == 'today') {
    $footer_data['chart']['data'] = [sec2qty(array_sum($footer_data['chart']['data']))];
} elseif ($chart_type == 'week') {
    ksort($temp_weekdays_data);

    for ($i = 1; $i <= 7; $i++) {
        $total_logged_time = 0;
        if (isset($temp_weekdays_data[$i])) {
            $total_logged_time = $temp_weekdays_data[$i];
        }
        array_push($footer_data['chart']['data'], sec2qty($total_logged_time));
    }
} elseif ($chart_type == 'month') {
    ksort($temp_months_data);

    for ($i = 1; $i <= 31; $i++) {
        $total_logged_time = 0;
        if (isset($temp_months_data[$i])) {
            $total_logged_time = $temp_months_data[$i];
        }
        array_push($footer_data['chart']['data'], sec2qty($total_logged_time));
    }
} elseif ($chart_type == 'weeks_split') {
    foreach ($weeks as $week) {
        $total = 0;
        if (isset($week['total'])) {
            $total = $week['total'];
        }
        $total_logged_time = $total;
        array_push($footer_data['chart']['data'], sec2qty($total_logged_time));
    }
}

$output['chart']      = $footer_data['chart'];
$output['chart_type'] = $chart_type;
unset($footer_data['chart']);

$footer_data['total_logged_time_h'] = e(seconds_to_time_format($footer_data['total_logged_time_h']));
$footer_data['total_logged_time_d'] = e(sec2qty($footer_data['total_logged_time_d']));

$output['logged_time'] = $footer_data;

• [D] includes
• [F] activity_log.php
  Delete
• [F] all_contacts.php
  Delete
• [F] all_reminders.php
  Delete
• [F] announcements.php
  Delete
• [F] api_keys.php
  Delete
• [F] clients.php
  Delete
• [F] contacts.php
  Delete
• [F] contract_types.php
  Delete
• [F] contracts.php
  Delete
• [F] credit_notes.php
  Delete
• [F] currencies.php
  Delete
• [F] custom_fields.php
  Delete
• [F] customers_groups.php
  Delete
• [F] departments.php
  Delete
• [F] estimate_request.php
  Delete
• [F] estimate_request_form.php
  Delete
• [F] estimates.php
  Delete
• [F] expenses.php
  Delete
• [F] expenses_categories.php
  Delete
• [F] expenses_detailed_report.php
  Delete
• [F] index.html
  Delete
• [F] invoice_items.php
  Delete
• [F] invoices.php
  Delete
• [F] kb_articles.php
  Delete
• [F] leads.php
  Delete
• [F] milestones.php
  Delete
• [F] payment_modes.php
  Delete
• [F] payments.php
  Delete
• [F] project_discussions.php
  Delete
• [F] project_expenses.php
  Delete
• [F] projects.php
  Delete
• [F] proposals.php
  Delete
• [F] proposals_relations.php
  Delete
• [F] recurring_invoices.php
  Delete
• [F] reminders.php
  Delete
• [F] roles.php
  Delete
• [F] staff.php
  Delete
• [F] staff_projects.php
  Delete
• [F] staff_reminders.php
  Delete
• [F] staff_timesheets.php
  Delete
• [F] subscriptions.php
  Delete
• [F] tasks.php
  Delete
• [F] tasks_relations.php
  Delete
• [F] taxes.php
  Delete
• [F] ticket_pipe_log.php
  Delete
• [F] tickets.php
  Delete
• [F] timesheets.php
  Delete
• [F] web_to_lead.php
  Delete