asterisk -rx "core show version"
mysql -u root -p -e "USE asterisk; SHOW TABLES LIKE 'vicidial%';" | head -20

vicidial_log - Individual call records
vicidial_closer_log - Detailed agent call details
vicidial_agent_log - Agent login/logout sessions

-- vicidial_log: Master call records
SELECT 
    call_date,
    caller_id_number,
    called_party_number,
    agent_only,
    talk_sec,
    wait_sec,
    status
FROM vicidial_log 
WHERE call_date >= DATE(NOW()) - INTERVAL 7 DAY
LIMIT 5;

-- vicidial_closer_log: Agent-specific call handling
SELECT
    call_date,
    user,
    call_length,
    pause_sec,
    status,
    lead_id,
    list_id
FROM vicidial_closer_log
WHERE call_date >= DATE(NOW()) - INTERVAL 1 DAY
AND user = 'AGENT001'
ORDER BY call_date DESC;

-- vicidial_agent_log: Login sessions and availability
SELECT
    agent_log_id,
    user,
    server_ip,
    event_time,
    event_type,
    pause_type,
    status
FROM vicidial_agent_log
WHERE user = 'AGENT001'
AND event_time >= DATE(NOW()) - INTERVAL 1 DAY
ORDER BY event_time DESC;

http://your-vicidial-server/vicidial/admin.php?action=show_reports

Reports > Agent Performance > Select Date Range > Select Agents

SELECT 
    user AS agent_name,
    DATE(call_date) AS call_date,
    COUNT(*) AS total_calls,
    SUM(CASE WHEN talk_sec > 0 THEN 1 ELSE 0 END) AS connected_calls,
    ROUND(AVG(talk_sec), 2) AS avg_talk_sec,
    SUM(talk_sec) AS total_talk_sec,
    ROUND(AVG(wait_sec), 2) AS avg_wait_sec,
    SUM(pause_sec) / 60 AS total_pause_min,
    COUNT(DISTINCT(DATE_FORMAT(call_date, '%Y-%m-%d %H:00:00'))) AS hours_worked
FROM vicidial_closer_log
WHERE user = 'AGENT001'
AND call_date >= '2024-01-15 00:00:00'
AND call_date < '2024-01-16 00:00:00'
GROUP BY user, DATE(call_date);

agent_name | call_date  | total_calls | connected_calls | avg_talk_sec | total_talk_sec | avg_wait_sec | total_pause_min | hours_worked
AGENT001   | 2024-01-15 | 87          | 67              | 312.45       | 27183          | 12.56        | 45.33           | 8

SELECT 
    u.user,
    u.full_name,
    DATE(vcl.call_date) AS work_date,
    COUNT(*) AS total_calls,
    ROUND(COUNT(*) / (SELECT COUNT(*) FROM vicidial_closer_log 
        WHERE user = u.user AND DATE(call_date) = DATE(vcl.call_date)) * 100, 2) AS call_pct,
    SUM(CASE WHEN vcl.talk_sec > 0 THEN 1 ELSE 0 END) AS connected_calls,
    ROUND(AVG(vcl.talk_sec), 1) AS avg_talk_sec,
    SUM(vcl.talk_sec) AS total_talk_sec,
    ROUND(AVG(vcl.wait_sec), 1) AS avg_wait_sec,
    GROUP_CONCAT(DISTINCT vcl.status ORDER BY vcl.status) AS call_statuses
FROM vicidial_closer_log vcl
INNER JOIN vicidial_users u ON vcl.user = u.user
WHERE DATE(vcl.call_date) = '2024-01-15'
AND vcl.user IN ('AGENT001', 'AGENT002', 'AGENT003')
GROUP BY u.user, u.full_name, DATE(vcl.call_date)
ORDER BY total_calls DESC;

SELECT 
    user,
    status,
    COUNT(*) AS count,
    ROUND(COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY user) * 100, 2) AS pct_of_total,
    ROUND(AVG(talk_sec), 1) AS avg_talk_time,
    SUM(talk_sec) AS total_talk_time
FROM vicidial_closer_log
WHERE DATE(call_date) = CURDATE()
AND user = 'AGENT001'
GROUP BY user, status
ORDER BY count DESC;

SELECT 
    aal.event_time,
    aal.event_type,
    aal.status,
    aal.pause_type,
    aal.data,
    aal.server_ip
FROM vicidial_agent_log aal
WHERE aal.user = 'AGENT001'
AND aal.event_time >= '2024-01-15 08:00:00'
AND aal.event_time < '2024-01-15 17:00:00'
ORDER BY aal.event_time ASC;

SELECT 
    user,
    DATE(event_time) AS work_date,
    TIMEDIFF(
        MAX(CASE WHEN event_type = 'LOGOUT' THEN event_time END),
        MIN(CASE WHEN event_type = 'LOGIN' THEN event_time END)
    ) AS total_session_time,
    SEC_TO_TIME(
        SUM(CASE 
            WHEN event_type = 'PAUSE' THEN 
                TIMESTAMPDIFF(SECOND, event_time, 
                    LEAD(event_time) OVER (PARTITION BY user ORDER BY event_time))
            ELSE 0 
        END)
    ) AS total_pause_time,
    ROUND(
        (1 - SUM(CASE 
            WHEN event_type = 'PAUSE' THEN 
                TIMESTAMPDIFF(SECOND, event_time, 
                    LEAD(event_time) OVER (PARTITION BY user ORDER BY event_time))
            ELSE 0 
        END) / TIMESTAMPDIFF(SECOND,
            MIN(CASE WHEN event_type = 'LOGIN' THEN event_time END),
            MAX(CASE WHEN event_type = 'LOGOUT' THEN event_time END))
        ) * 100, 2
    ) AS availability_pct
FROM vicidial_agent_log
WHERE DATE(event_time) = '2024-01-15'
AND user = 'AGENT001'
GROUP BY user, DATE(event_time);

<?php
// agent_performance.php - Custom Agent Performance Report
session_start();
require_once('/var/www/html/vicidial/admin/dbconnect.php');

// Verify admin login
if ($_SESSION['admin_user'] != 'Y') {
    header('Location: /vicidial/index.php');
    exit;
}

$agent_id = isset($_GET['agent_id']) ? sanitize($_GET['agent_id']) : '';
$report_date = isset($_GET['date']) ? sanitize($_GET['date']) : date('Y-m-d');

if (empty($agent_id)) {
    die('Agent ID required');
}

// Connect to ViciDial database
$mysqli = new mysqli($db_host, $db_user, $db_pass, $db_name);
if ($mysqli->connect_error) {
    die('Database connection failed: ' . $mysqli->connect_error);
}

// Fetch agent details
$agent_query = "SELECT user, full_name, user_level FROM vicidial_users WHERE user = ?";
$stmt = $mysqli->prepare($agent_query);
$stmt->bind_param('s', $agent_id);
$stmt->execute();
$agent_result = $stmt->get_result()->fetch_assoc();

if (!$agent_result) {
    die('Agent not found');
}

// Main performance metrics
$perf_query = "
    SELECT 
        user,
        COUNT(*) AS total_calls,
        SUM(CASE WHEN talk_sec > 0 THEN 1 ELSE 0 END) AS connected_calls,
        ROUND(AVG(talk_sec), 2) AS avg_talk_sec,
        SUM(talk_sec) AS total_talk_sec,
        ROUND(AVG(wait_sec), 2) AS avg_wait_sec,
        SUM(pause_sec) AS total_pause_sec,
        MIN(call_date) AS first_call,
        MAX(call_date) AS last_call
    FROM vicidial_closer_log
    WHERE user = ? AND DATE(call_date) = ?
    GROUP BY user
";

$stmt = $mysqli->prepare($perf_query);
$stmt->bind_param('ss', $agent_id, $report_date);
$stmt->execute();
$perf_data = $stmt->get_result()->fetch_assoc();

// Status breakdown
$status_query = "
    SELECT 
        status,
        COUNT(*) AS count,
        ROUND(COUNT(*) / (
            SELECT COUNT(*) FROM vicidial_closer_log 
            WHERE user = ? AND DATE(call_date) = ?
        ) * 100, 2) AS percentage,
        ROUND(AVG(talk_sec), 1) AS avg_talk_sec
    FROM vicidial_closer_log
    WHERE user = ? AND DATE(call_date) = ?
    GROUP BY status
    ORDER BY count DESC
";

$stmt = $mysqli->prepare($status_query);
$stmt->bind_param('ssss', $agent_id, $report_date, $agent_id, $report_date);
$stmt->execute();
$status_results = $stmt->get_result();

// Output HTML report
?>
<!DOCTYPE html>
<html>
<head>
    <title>Agent Performance Report - <?php echo htmlspecialchars($agent_result['full_name']); ?></title>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; }
        .report-header { background: #f0f0f0; padding: 15px; border-radius: 5px; margin-bottom: 20px; }
        .metric { display: inline-block; margin: 10px 20px 10px 0; }
        table { border-collapse: collapse; width: 100%; margin: 20px 0; }
        th, td { border: 1px solid #ddd; padding: 10px; text-align: left; }
        th { background-color: #4CAF50; color: white; }
        tr:nth-child(even) { background-color: #f2f2f2; }
    </style>
</head>
<body>
    <div class="report-header">
        <h1>Agent Performance Report</h1>
        <p><strong>Agent:</strong> <?php echo htmlspecialchars($agent_result['full_name']) . ' (' . htmlspecialchars($agent_id) . ')'; ?></p>
        <p><strong>Date:</strong> <?php echo htmlspecialchars($report_date); ?></p>
    </div>

    <div class="metrics">
        <?php if ($perf_data): ?>
        <div class="metric">
            <strong>Total Calls:</strong> <?php echo $perf_data['total_calls']; ?>
        </div>
        <div class="metric">
            <strong>Connected Calls:</strong> <?php echo $perf_data['connected_calls']; ?> 
            (<?php echo round(($perf_data['connected_calls']/$perf_data['total_calls'])*100, 1); ?>%)
        </div>
        <div class="metric">
            <strong>Avg Talk Time:</strong> <?php echo gmdate('H:i:s', $perf_data['avg_talk_sec']); ?>
        </div>
        <div class="metric">
            <strong>Total Talk Time:</strong> <?php echo gmdate('H:i:s', $perf_data['total_talk_sec']); ?>
        </div>
        <div class="metric">
            <strong>Avg Wait Time:</strong> <?php echo round($perf_data['avg_wait_sec']); ?>s
        </div>
        <div class="metric">
            <strong>Total Pause Time:</strong> <?php echo gmdate('H:i:s', $perf_data['total_pause_sec']); ?>
        </div>
        <?php else: ?>
        <p>No data available for this agent on the selected date.</p>
        <?php endif; ?>
    </div>

    <h2>Call Status Breakdown</h2>
    <table>
        <tr>
            <th>Status</th>
            <th>Count</th>
            <th>Percentage</th>
            <th>Avg Talk Time</th>
        </tr>
        <?php while ($row = $status_results->fetch_assoc()): ?>
        <tr>
            <td><?php echo htmlspecialchars($row['status']); ?></td>
            <td><?php echo $row['count']; ?></td>
            <td><?php echo $row['percentage']; ?>%</td>
            <td><?php echo gmdate('H:i:s', $row['avg_talk_sec']); ?></td>
        </tr>
        <?php endwhile; ?>
    </table>
</body>
</html>
<?php
$mysqli->close();

function sanitize($input) {
    return htmlspecialchars(strip_tags($input));
}
?>

http://your-vicidial-server/vicidial/reports/agent_performance.php?agent_id=AGENT001&date=2024-01-15

#!/bin/bash
# vicidial_export_performance.sh - Export agent performance to CSV

DB_HOST="localhost"
DB_USER="vicidial"
DB_PASS="your_password"
DB_NAME="asterisk"
REPORT_DATE="${1:-$(date +%Y-%m-%d)}"
OUTPUT_DIR="/tmp/vicidial_reports"
OUTPUT_FILE="${OUTPUT_DIR}/agent_performance_${REPORT_DATE}.csv"

mkdir -p "$OUTPUT_DIR"

mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" \
  -e "SELECT 
        u.user AS 'Agent ID',
        u.full_name AS 'Agent Name',
        COUNT(*) AS 'Total Calls',
        SUM(CASE WHEN vcl.talk_sec > 0 THEN 1 ELSE 0 END) AS 'Connected',
        ROUND(COUNT(*) / SUM(CASE WHEN vcl.talk_sec > 0 THEN 1 ELSE 0 END) * 100, 1) AS 'Connect %',
        ROUND(AVG(vcl.talk_sec), 1) AS 'Avg Talk Sec',
        SEC_TO_TIME(SUM(vcl.talk_sec)) AS 'Total Talk Time',
        ROUND(AVG(vcl.wait_sec), 1) AS 'Avg Wait Sec',
        SEC_TO_TIME(SUM(vcl.pause_sec)) AS 'Total Pause Time'
      FROM vicidial_closer_log vcl
      INNER JOIN vicidial_users u ON vcl.user = u.user
      WHERE DATE(vcl.call_date) = '$REPORT_DATE'
      GROUP BY vcl.user, u.full_name
      ORDER BY COUNT(*) DESC" \
  --batch --skip-column-names \
  | sed 's/\t/","/g;s/^/"/;s/$/"/' > "$OUTPUT_FILE"

echo "Report exported to: $OUTPUT_FILE"
ls -lh "$OUTPUT_FILE"

chmod +x /usr/local/bin/vicidial_export_performance.sh
/usr/local/bin/vicidial_export_performance.sh 2024-01-15

#!/bin/bash
OUTPUT_FILE="/tmp/vicidial_reports/performance_$(date +%Y%m%d_%H%M%S).csv"

{
  echo "Agent ID,Agent Name,Total Calls,Connected,Connect %,Avg Talk Sec,Total Talk Time,Avg Wait Sec,Total Pause Time"

  mysql -h localhost -u vicidial -p"password" asterisk -N -e \
    "SELECT 
      u.user,
      u.full_name,
      COUNT(*),
      SUM(CASE WHEN vcl.talk_sec > 0 THEN 1 ELSE 0 END),
      ROUND(SUM(CASE WHEN vcl.talk_sec > 0 THEN 1 ELSE 0 END) / COUNT(*) * 100, 1),
      ROUND(AVG(vcl.talk_sec), 1),
      SEC_TO_TIME(SUM(vcl.talk_sec)),
      ROUND(AVG(vcl.wait_sec), 1),
      SEC_TO_TIME(SUM(vcl.pause_sec))
    FROM vicidial_closer_log vcl
    INNER JOIN vicidial_users u ON vcl.user = u.user
    WHERE DATE(vcl.call_date) = CURDATE()
    GROUP BY vcl.user, u.full_name
    ORDER BY COUNT(*) DESC" | sed 's/\t/,/g'
} > "$OUTPUT_FILE"

echo "Report saved: $OUTPUT_FILE"

SELECT 
    user,
    ROUND(AVG(talk_sec + pause_sec), 2) AS aht_seconds,
    SEC_TO_TIME(ROUND(AVG(talk_sec + pause_sec))) AS aht_formatted,
    COUNT(*) AS calls_handled
FROM vicidial_closer_log
WHERE DATE(call_date) = CURDATE()
AND talk_sec > 0
GROUP BY user
ORDER BY aht_seconds DESC;

SELECT 
    vcl1.user,
    COUNT(DISTINCT vcl1.lead_id) AS total_unique_leads,
    COUNT(DISTINCT CASE 
        WHEN NOT EXISTS (
            SELECT 1 FROM vicidial_closer_log vcl2
            WHERE vcl2.lead_id = vcl1.lead_id
            AND vcl2.call_date > vcl1.call_date
            AND DATE(vcl2.call_date) = DATE(vcl1.call_date)
        )
        THEN vcl1.lead_id
    END) AS first_contact_resolutions,
    ROUND(
        COUNT(DISTINCT CASE 
            WHEN NOT EXISTS (
                SELECT 1 FROM vicidial_closer_log vcl2
                WHERE vcl2.lead_id = vcl1.lead_id
                AND vcl2.call_date > vcl1.call_date
                AND DATE(vcl2.call_date) = DATE(vcl1.call_date)
            )
            THEN vcl1.lead_id
        END) / COUNT(DISTINCT vcl1.lead_id) * 100, 2
    ) AS fcr_percentage
FROM vicidial_closer_log vcl1
WHERE DATE(vcl1.call_date) = CURDATE()
AND vcl1.talk_sec > 0
GROUP BY vcl1.user
ORDER BY fcr_percentage DESC;

SELECT 
    u.user,
    u.full_name,
    DATE(vcl.call_date) AS report_date,
    COUNT(*) AS calls_reviewed,
    ROUND(AVG(CAST(vcl.customer_satisfaction AS DECIMAL(3,2))), 2) AS avg_satisfaction,
    COUNT(CASE WHEN CAST(vcl.customer_satisfaction AS DECIMAL(3,2)) >= 4 THEN 1 END) AS positive_surveys
FROM vicidial_closer_log vcl
INNER JOIN vicidial_users u ON vcl.user = u.user
WHERE vcl.customer_satisfaction IS NOT NULL
AND DATE(vcl.call_date) >= DATE(NOW()) - INTERVAL 7 DAY
GROUP BY u.user, u.full_name, DATE(vcl.call_date)
ORDER BY report_date DESC, avg_satisfaction DESC;

SELECT 
    u.user,
    u.full_name,
    ual.status,
    ual.pause_type,
    ual.event_time,
    TIMESTAMPDIFF(MINUTE, ual.event_time, NOW()) AS minutes_in_status,
    (SELECT COUNT(*) FROM vicidial_closer_log 
        WHERE user = u.user AND DATE(call_date) = CURDATE()) AS today_calls,
    (SELECT SUM(talk_sec) FROM vicidial_closer_log 
        WHERE user = u.user AND DATE(call_date) = CURDATE()) AS today_talk_sec
FROM vicidial_agent_log ual
INNER JOIN vicidial_users u ON ual.user = u.user
WHERE ual.event_time = (
    SELECT MAX(event_time) FROM vicidial_agent_log 
    WHERE user = u.user
)
ORDER BY u.full_name;

#!/bin/bash
# /usr/local/bin/email_performance_report.sh

RECIPIENT="supervisor@company.com"
SUBJECT="Daily Agent Performance Report - $(date +%Y-%m-%d)"
REPORT_DATE=$(date -d yesterday +%Y-%m-%d)

REPORT=$(mysql -h localhost -u vicidial -p"password" asterisk -N -e \
  "SELECT CONCAT(
    'Agent: ', u.full_name, ' | ',
    'Calls: ', COUNT(*), ' | ',
    'Avg Talk: ', SEC_TO_TIME(ROUND(AVG(talk_sec))), ' | ',
    'Connect %: ', ROUND(SUM(CASE WHEN talk_sec > 0 THEN 1 ELSE 0 END)/COUNT(*)*100, 1)
  )
  FROM vicidial_closer_log vcl
  INNER JOIN vicidial_users u ON vcl.user = u.user
  WHERE DATE(vcl.call_date) = '$REPORT_DATE'
  GROUP BY vcl.user
  ORDER BY COUNT(*) DESC")

echo "$REPORT" | mail -s "$SUBJECT" "$RECIPIENT"

0 8 * * * /usr/local/bin/email_performance_report.sh

mysql -u vicidial -p asterisk -e \
  "SELECT COUNT(*) FROM vicidial_closer_log WHERE call_date >= NOW() - INTERVAL 1 HOUR;"

ps aux | grep Asterisk
ps aux | grep vicidial
systemctl status vicidial

systemctl restart asterisk
systemctl restart vicidial

ALTER TABLE vicidial_closer_log 
ADD INDEX idx_user_date (user, call_date),
ADD INDEX idx_talk_sec (talk_sec),
ADD INDEX idx_status (status);

-- Verify indexes
SHOW INDEX FROM vicidial_closer_log;

SELECT 
    u.user,
    SUM(vcl.pause_sec) AS closer_log_pause,
    SUM(CASE WHEN aal.event_type = 'PAUSE' THEN 
        TIMESTAMPDIFF(SECOND, aal.event_time, 
            LEAD(aal.event_time) OVER (PARTITION BY aal.user ORDER BY aal.event_time))
    END) AS agent_log_pause
FROM vicidial_closer_log vcl
INNER JOIN vicidial_users u ON vcl.user = u.user
LEFT JOIN vicidial_agent_log aal ON u.user = aal.user 
    AND DATE(vcl.call_date) = DATE(aal.event_time)
WHERE DATE(vcl.call_date) = CURDATE()
GROUP BY u.user;

SELECT STRAIGHT_JOIN
    u.user,
    COUNT(*) AS total_calls
FROM vicidial_closer_log vcl
STRAIGHT_JOIN vicidial_users u ON vcl.user = u.user
WHERE vcl.call_date >= '2024-01-15 00:00:00'
AND vcl.call_date < '2024-01-16 00:00:00'
AND u.user_level > 1
GROUP BY u.user;

# Archive calls older than 90 days
mysql -u vicidial -p asterisk -e \
  "INSERT INTO vicidial_closer_log_archive 
   SELECT * FROM vicidial_closer_log 
   WHERE call_date < DATE_SUB(NOW(), INTERVAL 90 DAY);

   DELETE FROM vicidial_closer_log 
   WHERE call_date < DATE_SUB(NOW(), INTERVAL 90 DAY);"

SELECT COUNT(*) FROM vicidial_users WHERE user = 'AGENT001';

SELECT 
    vcl.user,
    COALESCE(u.full_name, vcl.user) AS agent_name,
    COUNT(*) AS calls
FROM vicidial_closer_log vcl
LEFT JOIN vicidial_users u ON vcl.user = u.user
WHERE DATE(vcl.call_date) = CURDATE()
GROUP BY vcl.user;

# Clear PHP cache if using OPcache
php -r "opcache_reset();"

# Restart web server
systemctl restart apache2
# or
systemctl restart nginx

asterisk -rx "show channels concise" | grep -i SIP