pdo.php

<?php

$dsn = '';
$username = '';
$password = '';

try {
	$pdo = new PDO($dsn, $username, $password);
	$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

	// Получаем всех клиентов
	$stmt = $pdo->query("SELECT c.id AS customer_id, c.city_id, c.first_order_date, cm.manager_id AS attraction_manager_id
        FROM customers c
        LEFT JOIN customer_to_manager_assign cm
        ON c.id = cm.customer_id AND c.city_id = cm.city_id
        WHERE c.first_order_date >= UNIX_TIMESTAMP('2024-06-03')");
	$customers = $stmt->fetchAll(PDO::FETCH_ASSOC);

	// Получаем всех персональных менеджеров
	$stmt = $pdo->query("SELECT id, fio, efficiency, attached_clients_count
        FROM managers
        WHERE role = 'Персональный менеджер'
        ORDER BY efficiency DESC");
	$personalManagers = $stmt->fetchAll(PDO::FETCH_ASSOC);

	// Получаем всех менеджеров по привлечению
	$stmt = $pdo->query("SELECT id, fio
        FROM managers
        WHERE role = 'Менеджер по привлечению'");
	$attractionManagers = $stmt->fetchAll(PDO::FETCH_ASSOC);

	// Организуем структуру менеджеров по привлечению
	$attractionManagersMap = [];
	foreach ($attractionManagers as $manager) {
		$attractionManagersMap[$manager['id']] = $manager['fio'];
	}

	// Организуем структуру персональных менеджеров
	$personalManagersMap = [];
	foreach ($personalManagers as &$manager) {
		$manager['assigned_clients'] = 0;
		$personalManagersMap[$manager['id']] = $manager;
	}

	// Отчеты
	$reportAttraction = [];
	$reportPersonal = [];

	// Перебираем клиентов и распределяем
	foreach ($customers as $customer) {
		$customerId = $customer['customer_id'];
		$cityId = $customer['city_id'];

		if ($customer['attraction_manager_id']) {
			$attractionManagerId = $customer['attraction_manager_id'];
		} else {
			// Пропустить клиента, если он не был прикреплен к "Менеджеру по привлечению"
			continue;
		}

		// Назначаем персонального менеджера
		foreach ($personalManagers as &$manager) {
			if ($manager['attached_clients_count'] + $manager['assigned_clients'] < 3000) {
				$newManagerId = $manager['id'];
				$manager['assigned_clients']++;
				break;
			}
		}

		// Обновляем данные в базе данных
		$pdo->prepare("UPDATE customer_to_manager_assign
            SET manager_id = :newManagerId
            WHERE customer_id = :customerId AND city_id = :cityId")
			->execute(['newManagerId' => $newManagerId, 'customerId' => $customerId, 'cityId' => $cityId]);

		// Для отчета
		$reportAttraction[$attractionManagerId]['fio'] = $attractionManagersMap[$attractionManagerId];
		$reportAttraction[$attractionManagerId]['clients_before'] = isset($reportAttraction[$attractionManagerId]['clients_before']) ? $reportAttraction[$attractionManagerId]['clients_before'] + 1 : 1;
		$reportAttraction[$attractionManagerId]['clients_1st_order'] = isset($reportAttraction[$attractionManagerId]['clients_1st_order']) ? $reportAttraction[$attractionManagerId]['clients_1st_order'] + 1 : 1;
		$reportAttraction[$attractionManagerId]['clients_after'] = isset($reportAttraction[$attractionManagerId]['clients_after']) ? $reportAttraction[$attractionManagerId]['clients_after'] - 1 : 0;

		$reportPersonal[$newManagerId]['fio'] = $personalManagersMap[$newManagerId]['fio'];
		$reportPersonal[$newManagerId]['clients_before'] = $personalManagersMap[$newManagerId]['attached_clients_count'];
		$reportPersonal[$newManagerId]['clients_after'] = isset($reportPersonal[$newManagerId]['clients_after']) ? $reportPersonal[$newManagerId]['clients_after'] + 1 : 1;

		$reportPersonal[$newManagerId]['new_clients'] = isset($reportPersonal[$newManagerId]['new_clients']) ? $reportPersonal[$newManagerId]['new_clients'] + 1 : 1;
	}

	foreach ($personalManagers as &$manager) {
		$personalManagerId = $manager['id'];
		$pdo->prepare("UPDATE managers
            SET attached_clients_count = attached_clients_count + :newClients
            WHERE id = :personalManagerId")
			->execute(['newClients' => $manager['assigned_clients'], 'personalManagerId' => $personalManagerId]);
	}

	// Сохранение отчетов в CSV
	function saveCSV($filename, $data)
	{
		$file = fopen($filename, 'w');

		if (!empty($data)) {
			fputcsv($file, array_keys(current($data)));
		}

		foreach ($data as $row) {
			fputcsv($file, $row);
		}

		fclose($file);
	}

	saveCSV('report_attraction.csv', $reportAttraction);
	saveCSV('report_personal.csv', $reportPersonal);

	// Вопросы:

	// Сколько Клиентов было доведено до 1-го заказа всеми "Менеджерами по привлечению"?
	$total_1st_orders = array_sum(array_column($reportAttraction, 'clients_1st_order'));
	echo "Клиентов, доведенных до первого заказа: $total_1st_orders\n";

	// Какому "Персональному менеджеру" больше всего досталось Клиентов? И сколько ему досталось новых Клиентов?
	usort($reportPersonal, function ($a, $b) {
		return $b['new_clients'] <=> $a['new_clients'];
	});
	$topPersonalManager = reset($reportPersonal);
	echo "Больше всего новых клиентов у персонального менеджера: {$topPersonalManager['fio']} ({$topPersonalManager['new_clients']} клиентов)\n";

} catch (PDOException $e) {
	echo 'Не удалось установить соединение: ' . $e->getMessage();
}