81 lines
2.5 KiB
PHP
81 lines
2.5 KiB
PHP
<?php
|
|
|
|
namespace App\Services\Commands;
|
|
|
|
use Exception;
|
|
use Illuminate\Http\Client\ConnectionException;
|
|
use Illuminate\Http\Client\RequestException;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Illuminate\Support\Facades\Http;
|
|
use Illuminate\Support\Facades\Log;
|
|
|
|
class SendSupervisorCheckStatsReportService
|
|
{
|
|
private string $url;
|
|
private string $channelName;
|
|
private string $provinceId;
|
|
private string $provinceName;
|
|
public function __construct()
|
|
{
|
|
$this->url = config('ProvinceInfo.center_ip');
|
|
$this->channelName = 'send_supervisor_check_stats_report';
|
|
$this->provinceId = config('ProvinceInfo.id');
|
|
$this->provinceName = config('ProvinceInfo.name');
|
|
}
|
|
|
|
/**
|
|
* @throws ConnectionException
|
|
* @throws RequestException
|
|
*/
|
|
public function execute(): void
|
|
{
|
|
try
|
|
{
|
|
$data = $this->prepare();
|
|
Http::post($this->url, $data)->throw();
|
|
Log::channel($this->channelName)->info(SendSupervisorCheckStatsReportService::class, [$this->provinceName, array_sum($data), 'successful']);
|
|
}
|
|
catch (Exception $e)
|
|
{
|
|
Log::channel($this->channelName)->error(SendSupervisorCheckStatsReportService::class, [$e->getMessage()]);
|
|
throw $e;
|
|
}
|
|
}
|
|
|
|
private function prepare(): array
|
|
{
|
|
$query = "
|
|
SELECT
|
|
{$this->provinceId} AS province_id,
|
|
'{$this->provinceName}' AS province_name,
|
|
od.date,
|
|
COUNT(*) AS reviews,
|
|
COUNT(CASE WHEN is_good = 1 THEN 1 END) AS good,
|
|
COUNT(CASE WHEN is_good = 0 THEN 1 END) AS bad,
|
|
COALESCE(cdr_data.calls, 0) AS calls
|
|
FROM (
|
|
SELECT date, is_good
|
|
FROM operator_dialogues
|
|
WHERE supervisor_id IS NOT NULL
|
|
AND create_at > NOW() - INTERVAL 3 DAY
|
|
) AS od
|
|
LEFT JOIN (
|
|
SELECT
|
|
DATE(calldate) AS d,
|
|
COUNT(CASE
|
|
WHEN dcontext = 'from-internal'
|
|
AND disposition = 'ANSWERED'
|
|
AND dst > 100 AND dst < 105
|
|
THEN 1 END) AS calls
|
|
FROM asteriskcdrdb.cdr
|
|
WHERE calldate > NOW() - INTERVAL 3 DAY
|
|
GROUP BY d
|
|
) AS cdr_data
|
|
ON od.date = cdr_data.d
|
|
GROUP BY od.date, cdr_data.calls
|
|
ORDER BY od.date DESC";
|
|
|
|
return DB::select($query);
|
|
}
|
|
}
|