633 خطوط
27 KiB
PHP
633 خطوط
27 KiB
PHP
<?php
|
|
|
|
namespace App\Services;
|
|
|
|
use App\Models\Call;
|
|
use App\Models\CallResult;
|
|
use App\Models\Campaign;
|
|
use App\Models\FollowUp;
|
|
use App\Models\ImportBatch;
|
|
use App\Models\Lead;
|
|
use App\Models\MergeHistory;
|
|
use App\Models\PipelineStage;
|
|
use App\Models\QualityReview;
|
|
use App\Models\Setting;
|
|
use App\Models\Team;
|
|
use App\Models\User;
|
|
use Carbon\Carbon;
|
|
|
|
class ReportService
|
|
{
|
|
public function agentPerformance(int $agentId, ?string $dateFrom = null, ?string $dateTo = null): array
|
|
{
|
|
$agent = User::findOrFail($agentId);
|
|
$callsQuery = Call::where('user_id', $agentId);
|
|
$leadsQuery = Lead::where('assigned_to', $agentId);
|
|
|
|
if ($dateFrom) {
|
|
$callsQuery->whereDate('created_at', '>=', $dateFrom);
|
|
$leadsQuery->whereDate('created_at', '>=', $dateFrom);
|
|
}
|
|
if ($dateTo) {
|
|
$callsQuery->whereDate('created_at', '<=', $dateTo);
|
|
$leadsQuery->whereDate('created_at', '<=', $dateTo);
|
|
}
|
|
|
|
$totalCalls = (clone $callsQuery)->count();
|
|
$answeredCalls = (clone $callsQuery)->whereIn('result', $this->successfulResultNames())->count();
|
|
$totalLeads = (clone $leadsQuery)->count();
|
|
$wonLeads = (clone $leadsQuery)->where('final_result', 'موفق')->count();
|
|
|
|
return [
|
|
'agent' => $agent->only(['id', 'name', 'email']),
|
|
'total_calls' => $totalCalls,
|
|
'answered_calls' => $answeredCalls,
|
|
'no_answer_calls' => $totalCalls - $answeredCalls,
|
|
'answer_rate' => $totalCalls > 0 ? round(($answeredCalls / $totalCalls) * 100, 1) : 0,
|
|
'total_leads_assigned' => $totalLeads,
|
|
'won_sales' => $wonLeads,
|
|
'conversion_rate' => $totalLeads > 0 ? round(($wonLeads / $totalLeads) * 100, 1) : 0,
|
|
'avg_call_duration' => (clone $callsQuery)->avg('duration') ?? 0,
|
|
];
|
|
}
|
|
|
|
public function teamPerformance(int $teamId, ?string $dateFrom = null, ?string $dateTo = null): array
|
|
{
|
|
$team = Team::with('members')->findOrFail($teamId);
|
|
$agentIds = $team->members->pluck('id')->toArray();
|
|
|
|
$totalCalls = Call::whereIn('user_id', $agentIds);
|
|
$totalLeads = Lead::whereIn('assigned_to', $agentIds);
|
|
|
|
if ($dateFrom) {
|
|
$totalCalls->whereDate('created_at', '>=', $dateFrom);
|
|
$totalLeads->whereDate('created_at', '>=', $dateFrom);
|
|
}
|
|
if ($dateTo) {
|
|
$totalCalls->whereDate('created_at', '<=', $dateTo);
|
|
$totalLeads->whereDate('created_at', '<=', $dateTo);
|
|
}
|
|
|
|
$wonLeads = (clone $totalLeads)->where('final_result', 'موفق')->count();
|
|
|
|
$agentPerformance = [];
|
|
foreach ($team->members as $member) {
|
|
$agentPerformance[] = $this->agentPerformance($member->id, $dateFrom, $dateTo);
|
|
}
|
|
|
|
return [
|
|
'team' => $team->only(['id', 'name']),
|
|
'member_count' => count($agentIds),
|
|
'total_calls' => $totalCalls->count(),
|
|
'total_leads' => $totalLeads->count(),
|
|
'won_sales' => $wonLeads,
|
|
'conversion_rate' => $totalLeads->count() > 0 ? round(($wonLeads / $totalLeads->count()) * 100, 1) : 0,
|
|
'agents' => $agentPerformance,
|
|
];
|
|
}
|
|
|
|
public function campaignReport(int $campaignId, ?string $dateFrom = null, ?string $dateTo = null): array
|
|
{
|
|
$campaign = Campaign::with('assignedAgents')->findOrFail($campaignId);
|
|
$leads = Lead::where('campaign_id', $campaignId);
|
|
|
|
if ($dateFrom) {
|
|
$leads->whereDate('created_at', '>=', $dateFrom);
|
|
}
|
|
if ($dateTo) {
|
|
$leads->whereDate('created_at', '<=', $dateTo);
|
|
}
|
|
|
|
$leadIds = (clone $leads)->pluck('id');
|
|
$calls = Call::whereIn('lead_id', $leadIds);
|
|
|
|
if ($dateFrom) {
|
|
$calls->whereDate('created_at', '>=', $dateFrom);
|
|
}
|
|
if ($dateTo) {
|
|
$calls->whereDate('created_at', '<=', $dateTo);
|
|
}
|
|
|
|
$wonLeads = (clone $leads)->where('final_result', 'موفق')->count();
|
|
$totalLeads = (clone $leads)->count();
|
|
|
|
return [
|
|
'campaign' => $campaign->only(['id', 'name', 'target', 'start_date', 'end_date', 'status']),
|
|
'total_leads' => $totalLeads,
|
|
'assigned_leads' => (clone $leads)->where('is_unassigned', false)->count(),
|
|
'total_calls' => (clone $calls)->count(),
|
|
'won_sales' => $wonLeads,
|
|
'target_progress' => $campaign->target > 0 ? round(($wonLeads / $campaign->target) * 100, 1) : 0,
|
|
'conversion_rate' => $totalLeads > 0 ? round(($wonLeads / $totalLeads) * 100, 1) : 0,
|
|
];
|
|
}
|
|
|
|
public function conversionReport(?string $dateFrom = null, ?string $dateTo = null, ?int $agentId = null, ?array $agentIds = null): array
|
|
{
|
|
$leadsQuery = Lead::query();
|
|
if ($dateFrom) {
|
|
$leadsQuery->whereDate('created_at', '>=', $dateFrom);
|
|
}
|
|
if ($dateTo) {
|
|
$leadsQuery->whereDate('created_at', '<=', $dateTo);
|
|
}
|
|
if ($agentId) {
|
|
$leadsQuery->where('assigned_to', $agentId);
|
|
} elseif (is_array($agentIds) && $agentIds !== []) {
|
|
$leadsQuery->whereIn('assigned_to', $agentIds);
|
|
} elseif (is_array($agentIds)) {
|
|
$leadsQuery->whereRaw('1 = 0');
|
|
}
|
|
|
|
$total = (clone $leadsQuery)->count();
|
|
$won = (clone $leadsQuery)->where('final_result', 'موفق')->count();
|
|
$lost = (clone $leadsQuery)->where('final_result', 'ناموفق')->count();
|
|
$today = Carbon::today();
|
|
$now = Carbon::now();
|
|
$stages = PipelineStage::orderBy('sort_order')->get();
|
|
$byStage = [];
|
|
|
|
foreach ($stages as $stage) {
|
|
$count = (clone $leadsQuery)->where('pipeline_stage_id', $stage->id)->count();
|
|
$byStage[] = [
|
|
'name' => $stage->name,
|
|
'color' => $stage->color,
|
|
'count' => $count,
|
|
'percentage' => $total > 0 ? round(($count / $total) * 100, 1) : 0,
|
|
];
|
|
}
|
|
|
|
return [
|
|
'total_leads' => $total,
|
|
'by_stage' => $byStage,
|
|
'conversion_rate' => $total > 0 ? round(($won / $total) * 100, 1) : 0,
|
|
'won_leads' => $won,
|
|
'lost_leads' => $lost,
|
|
'follow_ups_due_today' => $this->filterFollowUpsByAgent(
|
|
FollowUp::whereDate('scheduled_at', $today)->where('status', 'pending'),
|
|
$agentId,
|
|
$agentIds
|
|
)->count(),
|
|
'overdue_follow_ups' => $this->filterFollowUpsByAgent(
|
|
FollowUp::where('scheduled_at', '<', $now)->where('status', 'pending'),
|
|
$agentId,
|
|
$agentIds
|
|
)->count(),
|
|
'lost_reason_analysis' => (clone $leadsQuery)
|
|
->where('final_result', 'ناموفق')
|
|
->whereNotNull('lost_reason')
|
|
->selectRaw('lost_reason as name, count(*) as count')
|
|
->groupBy('lost_reason')
|
|
->orderByDesc('count')
|
|
->get(),
|
|
'proposal_sent_not_closed' => (clone $leadsQuery)
|
|
->whereHas('pipelineStage', fn ($q) => $q->where('slug', 'proposal_sent'))
|
|
->whereNull('final_result')
|
|
->count(),
|
|
];
|
|
}
|
|
|
|
public function callReport(?string $dateFrom = null, ?string $dateTo = null, ?int $agentId = null, ?array $agentIds = null): array
|
|
{
|
|
$query = Call::query();
|
|
if ($dateFrom) {
|
|
$query->whereDate('created_at', '>=', $dateFrom);
|
|
}
|
|
if ($dateTo) {
|
|
$query->whereDate('created_at', '<=', $dateTo);
|
|
}
|
|
if ($agentId) {
|
|
$query->where('user_id', $agentId);
|
|
} elseif (is_array($agentIds) && $agentIds !== []) {
|
|
$query->whereIn('user_id', $agentIds);
|
|
} elseif (is_array($agentIds)) {
|
|
$query->whereRaw('1 = 0');
|
|
}
|
|
|
|
$total = $query->count();
|
|
$results = CallResult::all();
|
|
$byResult = [];
|
|
|
|
foreach ($results as $result) {
|
|
$count = (clone $query)->where('result', $result->name)->count();
|
|
$byResult[] = [
|
|
'name' => $result->name,
|
|
'color' => $result->color,
|
|
'count' => $count,
|
|
'percentage' => $total > 0 ? round(($count / $total) * 100, 1) : 0,
|
|
];
|
|
}
|
|
|
|
return [
|
|
'total_calls' => $total,
|
|
'avg_duration' => $query->avg('duration') ?? 0,
|
|
'by_result' => $byResult,
|
|
];
|
|
}
|
|
|
|
public function followUpReport(?string $dateFrom = null, ?string $dateTo = null, ?int $agentId = null, ?array $agentIds = null): array
|
|
{
|
|
$query = FollowUp::query();
|
|
if ($dateFrom) {
|
|
$query->whereDate('scheduled_at', '>=', $dateFrom);
|
|
}
|
|
if ($dateTo) {
|
|
$query->whereDate('scheduled_at', '<=', $dateTo);
|
|
}
|
|
if ($agentId) {
|
|
$query->where('user_id', $agentId);
|
|
} elseif (is_array($agentIds) && $agentIds !== []) {
|
|
$query->whereIn('user_id', $agentIds);
|
|
} elseif (is_array($agentIds)) {
|
|
$query->whereRaw('1 = 0');
|
|
}
|
|
|
|
return [
|
|
'total' => $query->count(),
|
|
'pending' => (clone $query)->where('status', 'pending')->count(),
|
|
'completed' => (clone $query)->where('status', 'completed')->count(),
|
|
'overdue' => (clone $query)->where('is_overdue', true)->count(),
|
|
'items' => (clone $query)->with(['user:id,name', 'lead:id,company,first_name,last_name'])
|
|
->where(fn ($q) => $q->where('is_overdue', true)->orWhere('scheduled_at', '<', Carbon::now()))
|
|
->latest('scheduled_at')
|
|
->limit(50)
|
|
->get(),
|
|
];
|
|
}
|
|
|
|
public function lostReasonReport(?string $dateFrom = null, ?string $dateTo = null, ?int $agentId = null, ?array $agentIds = null): array
|
|
{
|
|
$query = $this->scopedLeads($dateFrom, $dateTo, $agentId, $agentIds)
|
|
->where('final_result', 'ناموفق')
|
|
->whereNotNull('lost_reason');
|
|
|
|
$total = (clone $query)->count();
|
|
|
|
return [
|
|
'total_lost' => $total,
|
|
'by_reason' => (clone $query)->selectRaw('lost_reason as name, count(*) as count')
|
|
->groupBy('lost_reason')
|
|
->orderByDesc('count')
|
|
->get()
|
|
->map(fn ($row) => [
|
|
'name' => $row->name,
|
|
'count' => (int) $row->count,
|
|
'percentage' => $total > 0 ? round(($row->count / $total) * 100, 1) : 0,
|
|
])->values(),
|
|
];
|
|
}
|
|
|
|
public function sourcePerformanceReport(?string $dateFrom = null, ?string $dateTo = null, ?int $agentId = null, ?array $agentIds = null): array
|
|
{
|
|
$query = $this->scopedLeads($dateFrom, $dateTo, $agentId, $agentIds);
|
|
|
|
return [
|
|
'sources' => (clone $query)
|
|
->selectRaw("COALESCE(source, 'نامشخص') as source, count(*) as total, sum(case when final_result = 'موفق' then 1 else 0 end) as won, sum(case when final_result = 'ناموفق' then 1 else 0 end) as lost")
|
|
->groupBy('source')
|
|
->orderByDesc('total')
|
|
->get()
|
|
->map(fn ($row) => [
|
|
'source' => $row->source,
|
|
'total' => (int) $row->total,
|
|
'won' => (int) $row->won,
|
|
'lost' => (int) $row->lost,
|
|
'conversion_rate' => $row->total > 0 ? round(($row->won / $row->total) * 100, 1) : 0,
|
|
])->values(),
|
|
];
|
|
}
|
|
|
|
public function duplicateLeadsReport(?string $dateFrom = null, ?string $dateTo = null, ?int $agentId = null, ?array $agentIds = null): array
|
|
{
|
|
$query = $this->scopedLeads($dateFrom, $dateTo, $agentId, $agentIds);
|
|
$duplicatePhones = (clone $query)
|
|
->whereNotNull('phone')
|
|
->selectRaw('phone, count(*) as count')
|
|
->groupBy('phone')
|
|
->havingRaw('count(*) > 1')
|
|
->orderByDesc('count')
|
|
->limit(50)
|
|
->get();
|
|
$duplicateEmails = (clone $query)
|
|
->whereNotNull('email')
|
|
->selectRaw('email, count(*) as count')
|
|
->groupBy('email')
|
|
->havingRaw('count(*) > 1')
|
|
->orderByDesc('count')
|
|
->limit(50)
|
|
->get();
|
|
$duplicateCompanies = (clone $query)
|
|
->whereNotNull('company')
|
|
->selectRaw('company, count(*) as count')
|
|
->groupBy('company')
|
|
->havingRaw('count(*) > 1')
|
|
->orderByDesc('count')
|
|
->limit(50)
|
|
->get();
|
|
|
|
return [
|
|
'phone_duplicates' => $duplicatePhones,
|
|
'email_duplicates' => $duplicateEmails,
|
|
'company_duplicates' => $duplicateCompanies,
|
|
'merge_history_count' => MergeHistory::where('entity_type', 'like', '%Lead%')->count(),
|
|
];
|
|
}
|
|
|
|
public function importQualityReport(?string $dateFrom = null, ?string $dateTo = null): array
|
|
{
|
|
$query = ImportBatch::with(['user:id,name', 'campaign:id,name']);
|
|
if ($dateFrom) {
|
|
$query->whereDate('created_at', '>=', $dateFrom);
|
|
}
|
|
if ($dateTo) {
|
|
$query->whereDate('created_at', '<=', $dateTo);
|
|
}
|
|
|
|
$batches = $query->latest()->limit(50)->get();
|
|
$totalRows = max($batches->sum('total_rows'), 1);
|
|
|
|
return [
|
|
'total_batches' => $batches->count(),
|
|
'total_rows' => $batches->sum('total_rows'),
|
|
'imported_rows' => $batches->sum('imported_rows'),
|
|
'failed_rows' => $batches->sum('failed_rows'),
|
|
'skipped_rows' => $batches->sum('skipped_rows'),
|
|
'success_rate' => round(($batches->sum('imported_rows') / $totalRows) * 100, 1),
|
|
'batches' => $batches,
|
|
];
|
|
}
|
|
|
|
public function callQualityReport(?string $dateFrom = null, ?string $dateTo = null, ?int $agentId = null, ?array $agentIds = null): array
|
|
{
|
|
$query = QualityReview::with(['agent:id,name', 'reviewer:id,name']);
|
|
if ($dateFrom) {
|
|
$query->whereDate('created_at', '>=', $dateFrom);
|
|
}
|
|
if ($dateTo) {
|
|
$query->whereDate('created_at', '<=', $dateTo);
|
|
}
|
|
if ($agentId) {
|
|
$query->where('agent_id', $agentId);
|
|
} elseif (is_array($agentIds) && $agentIds !== []) {
|
|
$query->whereIn('agent_id', $agentIds);
|
|
} elseif (is_array($agentIds)) {
|
|
$query->whereRaw('1 = 0');
|
|
}
|
|
|
|
return [
|
|
'reviewed_calls' => (clone $query)->count(),
|
|
'average_score' => round((float) ((clone $query)->avg('overall_score') ?? 0), 1),
|
|
'low_score_count' => (clone $query)->where('overall_score', '<', 60)->count(),
|
|
'by_agent' => (clone $query)->selectRaw('agent_id, count(*) as reviews, avg(overall_score) as average_score')
|
|
->groupBy('agent_id')
|
|
->with('agent:id,name')
|
|
->get(),
|
|
'recent_reviews' => (clone $query)->latest()->limit(20)->get(),
|
|
];
|
|
}
|
|
|
|
public function bestContactTimeReport(?string $dateFrom = null, ?string $dateTo = null, ?int $agentId = null, ?array $agentIds = null): array
|
|
{
|
|
$query = Call::query();
|
|
if ($dateFrom) {
|
|
$query->whereDate('created_at', '>=', $dateFrom);
|
|
}
|
|
if ($dateTo) {
|
|
$query->whereDate('created_at', '<=', $dateTo);
|
|
}
|
|
if ($agentId) {
|
|
$query->where('user_id', $agentId);
|
|
} elseif (is_array($agentIds) && $agentIds !== []) {
|
|
$query->whereIn('user_id', $agentIds);
|
|
} elseif (is_array($agentIds)) {
|
|
$query->whereRaw('1 = 0');
|
|
}
|
|
|
|
$total = (clone $query)->count();
|
|
if ($total < 20) {
|
|
return [
|
|
'enough_data' => false,
|
|
'message' => 'برای تحلیل بهترین زمان تماس، حداقل ۲۰ تماس ثبتشده لازم است.',
|
|
'total_calls' => $total,
|
|
'by_hour' => [],
|
|
];
|
|
}
|
|
|
|
return [
|
|
'enough_data' => true,
|
|
'total_calls' => $total,
|
|
'by_hour' => (clone $query)->get(['created_at', 'result'])
|
|
->groupBy(fn (Call $call) => $call->created_at->hour)
|
|
->sortKeys()
|
|
->map(function ($calls, int $hour) {
|
|
$successful = $calls->whereIn('result', $this->successfulResultNames())->count();
|
|
|
|
return [
|
|
'hour' => $hour,
|
|
'total' => $calls->count(),
|
|
'successful' => $successful,
|
|
'success_rate' => $calls->count() > 0 ? round(($successful / $calls->count()) * 100, 1) : 0,
|
|
];
|
|
})->values(),
|
|
];
|
|
}
|
|
|
|
public function kpiDashboard(?string $dateFrom = null, ?string $dateTo = null, ?int $agentId = null, ?array $agentIds = null): array
|
|
{
|
|
$to = $dateTo ? Carbon::parse($dateTo)->endOfDay() : now()->endOfDay();
|
|
$from = $dateFrom ? Carbon::parse($dateFrom)->startOfDay() : $to->copy()->subDays(29)->startOfDay();
|
|
$periodDays = (int) $from->copy()->startOfDay()->diffInDays($to->copy()->startOfDay()) + 1;
|
|
$previousTo = $from->copy()->subSecond();
|
|
$previousFrom = $previousTo->copy()->subDays($periodDays - 1)->startOfDay();
|
|
$current = $this->kpiMetrics($from, $to, $agentId, $agentIds);
|
|
$previous = $this->kpiMetrics($previousFrom, $previousTo, $agentId, $agentIds);
|
|
$workingDays = $this->workingDaysBetween($from, $to);
|
|
|
|
$targets = [
|
|
'total_calls' => (int) (Setting::where('key', 'daily_call_target')->value('value') ?: 40) * $workingDays,
|
|
'successful_calls' => (int) (Setting::where('key', 'successful_call_target')->value('value') ?: 15) * $workingDays,
|
|
'completed_follow_ups' => (int) (Setting::where('key', 'follow_up_target')->value('value') ?: 20) * $workingDays,
|
|
'conversion_rate' => (int) (Setting::where('key', 'conversion_target_percent')->value('value') ?: 20),
|
|
];
|
|
$definitions = [
|
|
'total_calls' => ['label' => 'کل تماسها', 'unit' => 'تماس', 'link' => '/calls'],
|
|
'successful_calls' => ['label' => 'تماس موفق', 'unit' => 'تماس', 'link' => '/calls?result=positive'],
|
|
'completed_follow_ups' => ['label' => 'پیگیری انجامشده', 'unit' => 'پیگیری', 'link' => '/follow-ups?status=completed'],
|
|
'conversion_rate' => ['label' => 'نرخ تبدیل', 'unit' => 'درصد', 'link' => '/leads?final_result=موفق'],
|
|
'answer_rate' => ['label' => 'نرخ پاسخ', 'unit' => 'درصد', 'link' => '/calls'],
|
|
'avg_call_duration' => ['label' => 'میانگین مدت تماس', 'unit' => 'ثانیه', 'link' => '/calls'],
|
|
'won_sales' => ['label' => 'فروش موفق', 'unit' => 'فروش', 'link' => '/leads?final_result=موفق'],
|
|
'won_value' => ['label' => 'ارزش فروش', 'unit' => 'مبلغ', 'link' => '/leads?final_result=موفق'],
|
|
'overdue_follow_ups' => ['label' => 'پیگیری عقبافتاده', 'unit' => 'پیگیری', 'link' => '/follow-ups?status=overdue'],
|
|
];
|
|
$kpis = collect($definitions)->map(function (array $definition, string $key) use ($current, $previous, $targets): array {
|
|
$value = (float) ($current[$key] ?? 0);
|
|
$old = (float) ($previous[$key] ?? 0);
|
|
|
|
return $definition + [
|
|
'key' => $key,
|
|
'value' => $value,
|
|
'target' => $targets[$key] ?? null,
|
|
'target_percent' => isset($targets[$key]) && $targets[$key] > 0 ? round(($value / $targets[$key]) * 100, 1) : null,
|
|
'change_percent' => $old > 0 ? round((($value - $old) / $old) * 100, 1) : ($value > 0 ? 100 : 0),
|
|
'lower_is_better' => $key === 'overdue_follow_ups',
|
|
];
|
|
})->values()->all();
|
|
|
|
$calls = $this->scopeCallQuery(Call::whereBetween('created_at', [$from, $to]), $agentId, $agentIds)->get(['created_at', 'result']);
|
|
$followUps = $this->scopeFollowUpQuery(FollowUp::whereBetween('scheduled_at', [$from, $to]), $agentId, $agentIds)->get(['scheduled_at', 'status']);
|
|
$leads = $this->scopeLeadQuery(Lead::whereBetween('created_at', [$from, $to]), $agentId, $agentIds)->get(['created_at', 'final_result']);
|
|
$successNames = $this->successfulResultNames();
|
|
$trend = collect(range(0, $periodDays - 1))->map(function (int $offset) use ($from, $calls, $followUps, $leads, $successNames): array {
|
|
$date = $from->copy()->addDays($offset)->toDateString();
|
|
$dayCalls = $calls->filter(fn (Call $call) => $call->created_at->toDateString() === $date);
|
|
|
|
return [
|
|
'date' => $date,
|
|
'calls' => $dayCalls->count(),
|
|
'successful_calls' => $dayCalls->whereIn('result', $successNames)->count(),
|
|
'follow_ups' => $followUps->filter(fn (FollowUp $followUp) => $followUp->scheduled_at->toDateString() === $date && $followUp->status === 'completed')->count(),
|
|
'won' => $leads->filter(fn (Lead $lead) => $lead->created_at->toDateString() === $date && $lead->final_result === 'موفق')->count(),
|
|
];
|
|
})->values()->all();
|
|
|
|
$leaderboardIds = $agentId ? [$agentId] : (is_array($agentIds) && $agentIds !== [] ? $agentIds : User::role('agent')->pluck('id')->all());
|
|
$leaderboard = User::whereIn('id', $leaderboardIds)->orderBy('name')->get(['id', 'name'])->map(function (User $user) use ($from, $to): array {
|
|
$metric = $this->kpiMetrics($from, $to, $user->id, [$user->id]);
|
|
|
|
return [
|
|
'agent_id' => $user->id,
|
|
'agent_name' => $user->name,
|
|
'calls' => $metric['total_calls'],
|
|
'successful_calls' => $metric['successful_calls'],
|
|
'conversion_rate' => $metric['conversion_rate'],
|
|
'won_value' => $metric['won_value'],
|
|
];
|
|
})->sortByDesc(fn (array $row) => [$row['conversion_rate'], $row['successful_calls']])->values()->all();
|
|
|
|
return [
|
|
'range' => ['date_from' => $from->toDateString(), 'date_to' => $to->toDateString(), 'working_days' => $workingDays],
|
|
'kpis' => $kpis,
|
|
'trend' => $trend,
|
|
'leaderboard' => $leaderboard,
|
|
'updated_at' => now()->toISOString(),
|
|
];
|
|
}
|
|
|
|
private function kpiMetrics(Carbon $from, Carbon $to, ?int $agentId, ?array $agentIds): array
|
|
{
|
|
$calls = $this->scopeCallQuery(Call::whereBetween('created_at', [$from, $to]), $agentId, $agentIds);
|
|
$leads = $this->scopeLeadQuery(Lead::whereBetween('created_at', [$from, $to]), $agentId, $agentIds);
|
|
$followUps = $this->scopeFollowUpQuery(FollowUp::whereBetween('scheduled_at', [$from, $to]), $agentId, $agentIds);
|
|
$totalCalls = (clone $calls)->count();
|
|
$successfulCalls = (clone $calls)->whereIn('result', $this->successfulResultNames())->count();
|
|
$answeredCalls = (clone $calls)->whereNotNull('result')->whereNotIn('result', ['پاسخ نداد', 'اشغال بود', 'خاموش بود'])->count();
|
|
$totalLeads = (clone $leads)->count();
|
|
$wonSales = (clone $leads)->where('final_result', 'موفق')->count();
|
|
|
|
return [
|
|
'total_calls' => $totalCalls,
|
|
'successful_calls' => $successfulCalls,
|
|
'completed_follow_ups' => (clone $followUps)->where('status', 'completed')->count(),
|
|
'conversion_rate' => $totalLeads > 0 ? round(($wonSales / $totalLeads) * 100, 1) : 0,
|
|
'answer_rate' => $totalCalls > 0 ? round(($answeredCalls / $totalCalls) * 100, 1) : 0,
|
|
'avg_call_duration' => round((float) ((clone $calls)->avg('duration') ?? 0), 1),
|
|
'won_sales' => $wonSales,
|
|
'won_value' => round((float) (clone $leads)->where('final_result', 'موفق')->sum('deal_value'), 2),
|
|
'overdue_follow_ups' => $this->scopeFollowUpQuery(FollowUp::where('scheduled_at', '<', now())->where('status', 'pending'), $agentId, $agentIds)->count(),
|
|
];
|
|
}
|
|
|
|
private function workingDaysBetween(Carbon $from, Carbon $to): int
|
|
{
|
|
$codes = array_filter(explode(',', (string) (Setting::where('key', 'working_days')->value('value') ?: 'sat,sun,mon,tue,wed,thu')));
|
|
$map = ['sun' => 0, 'mon' => 1, 'tue' => 2, 'wed' => 3, 'thu' => 4, 'fri' => 5, 'sat' => 6];
|
|
$allowed = array_map(fn (string $code) => $map[$code] ?? -1, $codes);
|
|
$count = 0;
|
|
for ($date = $from->copy()->startOfDay(); $date->lte($to); $date->addDay()) {
|
|
if (in_array($date->dayOfWeek, $allowed, true)) {
|
|
$count++;
|
|
}
|
|
}
|
|
|
|
return max($count, 1);
|
|
}
|
|
|
|
private function scopeCallQuery($query, ?int $agentId, ?array $agentIds)
|
|
{
|
|
if ($agentId) {
|
|
$query->where('user_id', $agentId);
|
|
} elseif (is_array($agentIds) && $agentIds !== []) {
|
|
$query->whereIn('user_id', $agentIds);
|
|
} elseif (is_array($agentIds)) {
|
|
$query->whereRaw('1 = 0');
|
|
}
|
|
|
|
return $query;
|
|
}
|
|
|
|
private function scopeLeadQuery($query, ?int $agentId, ?array $agentIds)
|
|
{
|
|
if ($agentId) {
|
|
$query->where('assigned_to', $agentId);
|
|
} elseif (is_array($agentIds) && $agentIds !== []) {
|
|
$query->whereIn('assigned_to', $agentIds);
|
|
} elseif (is_array($agentIds)) {
|
|
$query->whereRaw('1 = 0');
|
|
}
|
|
|
|
return $query;
|
|
}
|
|
|
|
private function scopeFollowUpQuery($query, ?int $agentId, ?array $agentIds)
|
|
{
|
|
if ($agentId) {
|
|
$query->where('user_id', $agentId);
|
|
} elseif (is_array($agentIds) && $agentIds !== []) {
|
|
$query->whereIn('user_id', $agentIds);
|
|
} elseif (is_array($agentIds)) {
|
|
$query->whereRaw('1 = 0');
|
|
}
|
|
|
|
return $query;
|
|
}
|
|
|
|
private function filterFollowUpsByAgent($query, ?int $agentId, ?array $agentIds = null)
|
|
{
|
|
if ($agentId) {
|
|
$query->where('user_id', $agentId);
|
|
} elseif (is_array($agentIds) && $agentIds !== []) {
|
|
$query->whereIn('user_id', $agentIds);
|
|
} elseif (is_array($agentIds)) {
|
|
$query->whereRaw('1 = 0');
|
|
}
|
|
|
|
return $query;
|
|
}
|
|
|
|
private function scopedLeads(?string $dateFrom, ?string $dateTo, ?int $agentId, ?array $agentIds)
|
|
{
|
|
$query = Lead::query();
|
|
if ($dateFrom) {
|
|
$query->whereDate('created_at', '>=', $dateFrom);
|
|
}
|
|
if ($dateTo) {
|
|
$query->whereDate('created_at', '<=', $dateTo);
|
|
}
|
|
if ($agentId) {
|
|
$query->where('assigned_to', $agentId);
|
|
} elseif (is_array($agentIds) && $agentIds !== []) {
|
|
$query->whereIn('assigned_to', $agentIds);
|
|
} elseif (is_array($agentIds)) {
|
|
$query->whereRaw('1 = 0');
|
|
}
|
|
|
|
return $query;
|
|
}
|
|
|
|
private function successfulResultNames(): array
|
|
{
|
|
return CallResult::where('is_positive', true)->pluck('name')->all();
|
|
}
|
|
}
|