clientId = $clientId;
}
/**
* Execute the job.
*/
public function handle()
{
$this->logToFile("DAILY_AI_REPORT_START - Client ID: {$this->clientId}, Attempt: {$this->attempts()}");
try {
$connection = 'mysql_' . $this->clientId;
// Get client info
$client = Client::find($this->clientId);
if (!$client) {
$this->logToFile("ERROR: DAILY_AI_REPORT_CLIENT_NOT_FOUND - Client ID: {$this->clientId}");
return;
}
$clientName = $client->name ?? "Client #{$this->clientId}";
$this->logToFile("DAILY_AI_REPORT_CLIENT_FOUND - Client: {$clientName}");
// Get yesterday's date range (UTC)
$yesterday = Carbon::yesterday()->format('Y-m-d');
$startOfDay = $yesterday . ' 00:00:00';
$endOfDay = $yesterday . ' 23:59:59';
$this->logToFile("DAILY_AI_REPORT_DATE_RANGE - Date: {$yesterday}, Start: {$startOfDay}, End: {$endOfDay}");
// ========== AGGREGATE DATA BY ROUTE (IN/OUT) ==========
// Get incoming calls stats (route = 'IN')
$incomingStats = DB::connection($connection)
->table('cdr_ai')
->select([
DB::raw('COUNT(*) as total_calls'),
DB::raw('COALESCE(SUM(duration), 0) as total_duration_seconds'),
DB::raw('COALESCE(AVG(duration), 0) as avg_duration_seconds'),
DB::raw('COALESCE(SUM(unit_minute), 0) as total_billable_minutes'),
DB::raw('COALESCE(SUM(cost), 0) as total_cost')
])
->where('route', 'IN')
->whereBetween('start_time', [$startOfDay, $endOfDay])
->first();
// Get outgoing calls stats (route = 'OUT')
$outgoingStats = DB::connection($connection)
->table('cdr_ai')
->select([
DB::raw('COUNT(*) as total_calls'),
DB::raw('COALESCE(SUM(duration), 0) as total_duration_seconds'),
DB::raw('COALESCE(AVG(duration), 0) as avg_duration_seconds'),
DB::raw('COALESCE(SUM(unit_minute), 0) as total_billable_minutes'),
DB::raw('COALESCE(SUM(cost), 0) as total_cost')
])
->where('route', 'OUT')
->whereBetween('start_time', [$startOfDay, $endOfDay])
->first();
// Aggregate data from cdr_ai for yesterday (campaign-wise)
$campaignStats = DB::connection($connection)
->table('cdr_ai')
->select([
'campaign_id',
'route',
DB::raw('COUNT(*) as total_calls'),
DB::raw('COALESCE(SUM(duration), 0) as total_duration_seconds'),
DB::raw('COALESCE(AVG(duration), 0) as avg_duration_seconds'),
DB::raw('COALESCE(SUM(unit_minute), 0) as total_billable_minutes'),
DB::raw('COALESCE(SUM(cost), 0) as total_cost')
])
->whereBetween('start_time', [$startOfDay, $endOfDay])
->groupBy('campaign_id', 'route')
->get();
// Check if any data exists
$totalIncoming = $incomingStats->total_calls ?? 0;
$totalOutgoing = $outgoingStats->total_calls ?? 0;
if ($totalIncoming == 0 && $totalOutgoing == 0) {
$this->logToFile("DAILY_AI_REPORT_NO_DATA - Client ID: {$this->clientId}, Date: {$yesterday}");
return;
}
$this->logToFile("DAILY_AI_REPORT_DATA_FOUND - Incoming: {$totalIncoming}, Outgoing: {$totalOutgoing}");
// Get campaign names
$campaignIds = $campaignStats->pluck('campaign_id')->unique()->toArray();
$campaigns = DB::connection($connection)
->table('campaign')
->whereIn('id', $campaignIds)
->pluck('title', 'id')
->toArray();
// Calculate overall totals
$overallTotalCalls = $totalIncoming + $totalOutgoing;
$overallTotalDuration = ($incomingStats->total_duration_seconds ?? 0) + ($outgoingStats->total_duration_seconds ?? 0);
$overallBillableMinutes = ($incomingStats->total_billable_minutes ?? 0) + ($outgoingStats->total_billable_minutes ?? 0);
$overallTotalCost = ($incomingStats->total_cost ?? 0) + ($outgoingStats->total_cost ?? 0);
$overallAvgDuration = $overallTotalCalls > 0 ? $overallTotalDuration / $overallTotalCalls : 0;
$this->logToFile("DAILY_AI_REPORT_TOTALS - Calls: {$overallTotalCalls}, Duration: {$overallTotalDuration}s, Billable: {$overallBillableMinutes}min, Cost: {$overallTotalCost}");
// Determine recipient email
if ($this->SEND_TO_ADMIN) {
$recipientEmail = env('ADMIN_NOTIFY_EMAIL');
$recipientName = 'Admin';
$this->logToFile("DAILY_AI_REPORT_RECIPIENT_ADMIN - Email: {$recipientEmail}");
} else {
// Get client owner email
$owner = DB::connection('master')
->table('users')
->where('parent_id', $this->clientId)
->where('role', 'owner')
->first();
if (!$owner || empty($owner->email)) {
$this->logToFile("ERROR: DAILY_AI_REPORT_OWNER_NOT_FOUND - Client ID: {$this->clientId}");
return;
}
$recipientEmail = $owner->email;
$recipientName = $owner->name ?? 'Client';
$this->logToFile("DAILY_AI_REPORT_RECIPIENT_OWNER - Email: {$recipientEmail}");
}
if (empty($recipientEmail)) {
$this->logToFile("ERROR: DAILY_AI_REPORT_NO_RECIPIENT - Client ID: {$this->clientId}");
return;
}
// Build email body
$emailBody = $this->buildEmailBody(
$recipientName,
$clientName,
$yesterday,
$incomingStats,
$outgoingStats,
$campaignStats,
$campaigns,
$overallTotalCalls,
$overallTotalDuration,
$overallAvgDuration,
$overallBillableMinutes,
$overallTotalCost,
$client->currency_code ?? 'INR'
);
// Send email
$subject = "Daily AI Call Report - {$yesterday}";
send_sendgrid_mail($recipientEmail, $subject, $emailBody);
$this->logToFile("DAILY_AI_REPORT_EMAIL_SENT - Recipient: {$recipientEmail}, Date: {$yesterday}, Total Calls: {$overallTotalCalls}");
} catch (\Throwable $e) {
$this->logToFile("ERROR: DAILY_AI_REPORT_EXCEPTION - Client ID: {$this->clientId}, Error: {$e->getMessage()}");
Log::error("DailyAICallReportJob.handle.error", [
'clientId' => $this->clientId,
'error' => $e->getMessage(),
'trace' => $e->getTraceAsString()
]);
}
}
/**
* Log to file (file-based logging)
*/
private function logToFile($message)
{
$logFile = storage_path('app/daily_ai_call_report.log');
$timestamp = date('Y-m-d H:i:s');
$logMessage = "[{$timestamp}] {$message}\n";
file_put_contents($logFile, $logMessage, FILE_APPEND);
}
/**
* Format seconds to human-readable duration
*/
private function formatDuration($seconds)
{
$hours = floor($seconds / 3600);
$minutes = floor(($seconds % 3600) / 60);
$secs = $seconds % 60;
if ($hours > 0) {
return sprintf('%02d Hour %02d Minutes', $hours, $minutes);
}
return sprintf('%02d Minutes %02d Seconds', $minutes, $secs);
}
/**
* Format average duration (minutes and seconds)
*/
private function formatAvgDuration($seconds)
{
$minutes = floor($seconds / 60);
$secs = round($seconds % 60);
return sprintf('%d Minutes %02d Seconds', $minutes, $secs);
}
/**
* Build the HTML email body
*/
private function buildEmailBody(
$recipientName,
$clientName,
$date,
$incomingStats,
$outgoingStats,
$campaignStats,
$campaigns,
$totalCalls,
$totalDuration,
$avgDuration,
$billableMinutes,
$totalCost,
$currencyCode
) {
$currencySymbol = $currencyCode === 'INR' ? '₹' : '$';
// Incoming/Outgoing values
$inCalls = $incomingStats->total_calls ?? 0;
$inDuration = $incomingStats->total_duration_seconds ?? 0;
$inAvgDuration = $incomingStats->avg_duration_seconds ?? 0;
$inBillable = $incomingStats->total_billable_minutes ?? 0;
$inCost = $incomingStats->total_cost ?? 0;
$outCalls = $outgoingStats->total_calls ?? 0;
$outDuration = $outgoingStats->total_duration_seconds ?? 0;
$outAvgDuration = $outgoingStats->avg_duration_seconds ?? 0;
$outBillable = $outgoingStats->total_billable_minutes ?? 0;
$outCost = $outgoingStats->total_cost ?? 0;
// Build campaign-wise breakdown rows
$campaignRows = '';
$campaignGrouped = [];
foreach ($campaignStats as $stat) {
$cid = $stat->campaign_id;
if (!isset($campaignGrouped[$cid])) {
$campaignGrouped[$cid] = ['IN' => null, 'OUT' => null];
}
$campaignGrouped[$cid][$stat->route] = $stat;
}
foreach ($campaignGrouped as $campaignId => $routes) {
$campaignName = $campaigns[$campaignId] ?? "Campaign #{$campaignId}";
// Outgoing row
if ($routes['OUT']) {
$stat = $routes['OUT'];
$campaignRows .= "
| {$campaignName} |
OUT |
{$stat->total_calls} |
{$this->formatDuration($stat->total_duration_seconds)} |
{$this->formatAvgDuration($stat->avg_duration_seconds)} |
{$stat->total_billable_minutes} |
{$currencySymbol}" . number_format($stat->total_cost, 2) . " |
";
}
// Incoming row
if ($routes['IN']) {
$stat = $routes['IN'];
$campaignRows .= "
| {$campaignName} |
IN |
{$stat->total_calls} |
{$this->formatDuration($stat->total_duration_seconds)} |
{$this->formatAvgDuration($stat->avg_duration_seconds)} |
{$stat->total_billable_minutes} |
{$currencySymbol}" . number_format($stat->total_cost, 2) . " |
";
}
}
return "
Daily AI Call Report
Hello {$recipientName},
Please find below the summary of AI calling activity for {$clientName}:
Call Summary
| IN/OUT |
Calls |
Talk Time |
Avg Duration |
Billable Min |
Charge |
| Outgoing (OUT) |
{$outCalls} |
{$this->formatDuration($outDuration)} |
{$this->formatAvgDuration($outAvgDuration)} |
{$outBillable} |
{$currencySymbol}" . number_format($outCost, 2) . " |
| Incoming (IN) |
{$inCalls} |
{$this->formatDuration($inDuration)} |
{$this->formatAvgDuration($inAvgDuration)} |
{$inBillable} |
{$currencySymbol}" . number_format($inCost, 2) . " |
| TOTAL |
{$totalCalls} |
{$this->formatDuration($totalDuration)} |
{$this->formatAvgDuration($avgDuration)} |
{$billableMinutes} |
{$currencySymbol}" . number_format($totalCost, 2) . " |
Campaign-Wise Breakdown
| Campaign Name |
IN/OUT |
Calls |
Talk Time |
Avg Duration |
Billable Min |
Charge |
{$campaignRows}
This is an automated daily report generated at UTC 00:00.
Client: {$clientName}
";
}
}