<?php
namespace App\Command;
use App\Repository\EvaluationRepository;
use Doctrine\DBAL\Exception;
use Doctrine\ORM\EntityManagerInterface;
use Symfony\Component\Console\Attribute\AsCommand;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;
use Symfony\Component\Console\Style\SymfonyStyle;
#[AsCommand(
name: 'app:generate:script-chnage-status-ance14-e15',
description: 'this command will generate script to change status evaluation ance14-e15',
)]
class GenerateScriptChangeStatusANCE14E15EvalCommand extends Command
{
public function __construct(
private ?EntityManagerInterface $em = null,
private readonly EvaluationRepository $evaluationRepository,
)
{
parent::__construct();
}
/**
* @throws Exception
*/
protected function execute(InputInterface $input, OutputInterface $output): int
{
$io = new SymfonyStyle($input, $output);
$io->info('START');
$ids_sites = $this->getSitesPogressionZero();
$ids_sites = array_column($ids_sites, 'site_id');
dump( 'Nb sites: '. count($ids_sites));
dump( 'sites: '. implode(',', $ids_sites));
if (count($ids_sites) > 0) {
$ids_evaluations = $this->getEvaluations($ids_sites);
$ids_evaluations = array_column($ids_evaluations, 'id');
dump( 'Nb eval: '. count($ids_evaluations));
dump( 'evals: '. implode(',', $ids_evaluations));
$this->updateEvaluations($ids_evaluations);
}
$io->success('Done!');
return Command::SUCCESS;
}
/**
* @throws Exception
*/
private function getSitesPogressionZero(): array
{
$conn = $this->em->getConnection();
$columns = array_merge(['e.site_id'], EvaluationRepository::getDefaultStatColumnsReport());
$sql = sprintf("select %s
from evaluation e
where e.compliance_id in (2373493, 2373492)
group by e.site_id
having progression = 0 ", implode(',', $columns));
$stmt = $conn->prepare($sql);
return $stmt->executeQuery()->fetchAllAssociative();
}
/**
* @throws Exception
*/
private function getEvaluations($ids_sites): array
{
$conn = $this->em->getConnection();
$sql = sprintf("select id
from evaluation
where
compliance_id in ( 2373493, 2373492) and site_id in (%s)", implode(',', $ids_sites));
$stmt = $conn->prepare($sql);
return $stmt->executeQuery()->fetchAllAssociative();
}
/**
* @throws Exception
*/
private function updateEvaluations($ids_eval): void
{
$conn = $this->em->getConnection();
$sql = sprintf("update evaluation set status_id = 53, comment = NULL where id in (%s)", implode(',', $ids_eval));
$stmt = $conn->prepare($sql);
$stmt->executeQuery()->fetchAllAssociative();
}
}