Proyectos de Subversion LeadersLinked - Services

<?php
declare(strict_types = 1);
namespace LeadersLinked\Command;

use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
// use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;
use Laminas\Db\Adapter\AdapterInterface;
use Laminas\Log\LoggerInterface;
use LeadersLinked\Mapper\MicrolearningUserProgressMapper;
use LeadersLinked\Mapper\MicrolearningSlideMapper;
use LeadersLinked\Mapper\QueryMapper;
use LeadersLinked\Model\MicrolearningUserProgress;
use Laminas\Mvc\I18n\Translator;
use LeadersLinked\Cache\CacheInterface;

class RecalculateMicrolearningProgressCommand extends Command
{

    /**
     *
     * @var \Laminas\Db\Adapter\AdapterInterface
     */
    private $adapter;

    /**
     *
     * @var \LeadersLinked\Cache\CacheInterface
     */
    private $cache;

    /**
     *
     * @var \Laminas\Log\LoggerInterface
     */
    private $logger;

    /**
     *
     * @var array
     */
    private $config;

    /**
     *
     * @var \Laminas\Mvc\I18n\Translator
     */
    private $translator;

    /**
     *
     * @param \Laminas\Db\Adapter\AdapterInterface $adapter
     * @param \LeadersLinked\Cache\CacheInterface $cache
     * @param
     *            \Laminas\Log\LoggerInterface
     * @param array $config
     * @param \Laminas\Mvc\I18n\Translator $translator
     */
    public function __construct($adapter, $cache, $logger, $config, $translator)
    {
        $this->adapter = $adapter;
        $this->cache = $cache;
        $this->logger = $logger;
        $this->config = $config;
        $this->translator = $translator;

        parent::__construct();
    }

    protected function execute(InputInterface $input, OutputInterface $output): int
    {

        /**
         * *** INICIO TOPICOS DUPLICADOS ****
         */
        $output->writeln('Comenzamos a buscar los registros duplicados de los Tópicos');
        $queryMapper = QueryMapper::getInstance($this->adapter);

        $sql = " SELECT DISTINCT(user_id) AS user_id FROM " . MicrolearningUserProgressMapper::_TABLE;
        echo $sql . PHP_EOL;

        $record_users = $queryMapper->fetchAllUsingParameters($sql);

        foreach ($record_users as $record_user) {
            $user_id = $record_user['user_id'];

            $sql = " SELECT DISTINCT(topic_id) AS topic_id FROM " . MicrolearningUserProgressMapper::_TABLE;
            $sql .= " WHERE  type = '" . MicrolearningUserProgress::TYPE_TOPIC . "' ";
            $sql .= " AND user_id = $user_id";

            echo $sql . PHP_EOL;
            $records_topic = $queryMapper->fetchAllUsingParameters($sql);

            foreach ($records_topic as $record_topic) {
                $topic_id = $record_topic['topic_id'];

                $sql = " SELECT id FROM " . MicrolearningUserProgressMapper::_TABLE;
                $sql .= " WHERE  type = '" . MicrolearningUserProgress::TYPE_TOPIC . "' ";
                $sql .= " AND user_id = $user_id";
                $sql .= " AND topic_id = $topic_id";
                $sql .= " ORDER BY completed DESC LIMIT 0, 1";
                echo $sql . PHP_EOL;

                $record = $queryMapper->fetchOneUsingParameters($sql);
                $id = $record['id'];

                $sql = " DELETE FROM " . MicrolearningUserProgressMapper::_TABLE;
                $sql .= " WHERE type = '" . MicrolearningUserProgress::TYPE_TOPIC . "' ";
                $sql .= " AND user_id = $user_id ";
                $sql .= " AND topic_id = $topic_id ";
                $sql .= " AND id <> $id ";
                echo $sql . PHP_EOL;

                $queryMapper->delete($sql);
            }
        }
        $output->writeln('Terminamos de procesar los registros duplicados de los Tópicos');

        /**
         * *** FIN TOPICOS DUPLICADOS ****
         */

        /**
         * *** INICIO CAPSULAS DUPLICADOS ****
         */

        $output->writeln('Comenzamos a buscar los registros duplicados de las Capsulas');
        $queryMapper = QueryMapper::getInstance($this->adapter);

        $sql = " SELECT DISTINCT(user_id) AS user_id FROM " . MicrolearningUserProgressMapper::_TABLE;
        echo $sql . PHP_EOL;

        $record_users = $queryMapper->fetchAllUsingParameters($sql);

        foreach ($record_users as $record_user) {
            $user_id = $record_user['user_id'];

            $sql = " SELECT DISTINCT(capsule_id) AS capsule_id FROM " . MicrolearningUserProgressMapper::_TABLE;
            $sql .= " WHERE  type = '" . MicrolearningUserProgress::TYPE_CAPSULE . "' ";
            $sql .= " AND user_id = $user_id";

            echo $sql . PHP_EOL;
            $records_capsule = $queryMapper->fetchAllUsingParameters($sql);

            foreach ($records_capsule as $record_capsule) {
                $capsule_id = $record_capsule['capsule_id'];

                $sql = " SELECT id FROM " . MicrolearningUserProgressMapper::_TABLE;
                $sql .= " WHERE  type = '" . MicrolearningUserProgress::TYPE_CAPSULE . "' ";
                $sql .= " AND user_id = $user_id";
                $sql .= " AND capsule_id = $capsule_id";
                $sql .= " ORDER BY completed DESC LIMIT 0, 1";
                echo $sql . PHP_EOL;

                $record = $queryMapper->fetchOneUsingParameters($sql);
                $id = $record['id'];

                $sql = " DELETE FROM " . MicrolearningUserProgressMapper::_TABLE;
                $sql .= " WHERE type = '" . MicrolearningUserProgress::TYPE_CAPSULE . "' ";
                $sql .= " AND user_id = $user_id ";
                $sql .= " AND capsule_id = $capsule_id ";
                $sql .= " AND id <> $id ";
                echo $sql . PHP_EOL;

                $queryMapper->delete($sql);
            }
        }
        $output->writeln('Terminamos de procesar los registros duplicados de las Capsulas');

        /**
         * *** FIN CAPSULAS DUPLICADOS ****
         */

        /**
         * *** INICIO DIAPOSITIVAS DUPLICADOS ****
         */

        $output->writeln('Comenzamos a buscar los registros duplicados de las Diapositivas');
        $queryMapper = QueryMapper::getInstance($this->adapter);

        $sql = " SELECT DISTINCT(user_id) AS user_id FROM " . MicrolearningUserProgressMapper::_TABLE;
        echo $sql . PHP_EOL;

        $record_users = $queryMapper->fetchAllUsingParameters($sql);

        foreach ($record_users as $record_user) {
            $user_id = $record_user['user_id'];

            $sql = " SELECT DISTINCT(slide_id) AS slide_id FROM " . MicrolearningUserProgressMapper::_TABLE;
            $sql .= " WHERE  type = '" . MicrolearningUserProgress::TYPE_SLIDE . "' ";
            $sql .= " AND user_id = $user_id";

            echo $sql . PHP_EOL;
            $records_slides = $queryMapper->fetchAllUsingParameters($sql);

            foreach ($records_slides as $record_slide) {
                $slide_id = $record_slide['slide_id'];

                $sql = " SELECT id FROM " . MicrolearningUserProgressMapper::_TABLE;
                $sql .= " WHERE  type = '" . MicrolearningUserProgress::TYPE_SLIDE . "' ";
                $sql .= " AND user_id = $user_id";
                $sql .= " AND slide_id = $slide_id";
                $sql .= " ORDER BY completed DESC LIMIT 0, 1";
                echo $sql . PHP_EOL;

                $record = $queryMapper->fetchOneUsingParameters($sql);
                $id = $record['id'];

                $sql = " DELETE FROM " . MicrolearningUserProgressMapper::_TABLE;
                $sql .= " WHERE type = '" . MicrolearningUserProgress::TYPE_SLIDE . "' ";
                $sql .= " AND user_id = $user_id ";
                $sql .= " AND slide_id = $slide_id ";
                $sql .= " AND id <> $id ";
                echo $sql . PHP_EOL;

                $queryMapper->delete($sql);
            }
        }
        $output->writeln('Terminamos de procesar los registros duplicados de las Diapositivas');

        /**
         * *** FIN DIAPOSITIVAS DUPLICADOS ****
         */

        $progressMapper = MicrolearningUserProgressMapper::getInstance($this->adapter);
        $slideMapper = MicrolearningSlideMapper::getInstance($this->adapter);

        $output->writeln('Recalculo del progreso de los Tópicos');

        $records = $progressMapper->fetchAllTopics();

        foreach ($records as $record) {
            $total_slides = $slideMapper->fetchTotalCountByCompanyIdAndTopicId($record->company_id, $record->topic_id);
            $view_slides = $progressMapper->fetchCountAllSlideCompletedByUserIdAndTopicId($record->user_id, $record->topic_id);

            $record->total_slides = $total_slides;
            $record->view_slides = $view_slides;
            $record->progress = $total_slides > 0 ? (($view_slides * 100) / $total_slides) : 0;

            $sql = " UPDATE  " . MicrolearningUserProgressMapper::_TABLE;
            $sql .= " SET progress =  " . $record->progress;
            $sql .= " , total_slides =  " . $total_slides;
            $sql .= " , view_slides  =  " . $view_slides;
            $sql .= " WHERE id =  " . $record->id;
            echo $sql . PHP_EOL;

            $queryMapper->update($sql);
        }

        $output->writeln('Inicio del proceso de Recalculo de las Capsulas');
        $records = $progressMapper->fetchAllCapsules();
        foreach ($records as $record) {
            $total_slides = $slideMapper->fetchTotalCountByCompanyIdAndTopicIdAndCapsuleId($record->company_id, $record->topic_id, $record->capsule_id);
            $view_slides = $progressMapper->fetchCountAllSlideCompletedByUserIdAndTopicIdAndCapsuleId($record->user_id, $record->topic_id, $record->capsule_id);

            $record->total_slides = $total_slides;
            $record->view_slides = $view_slides;
            $record->progress = $total_slides > 0 ? (($view_slides * 100) / $total_slides) : 0;

            $sql = " UPDATE  " . MicrolearningUserProgressMapper::_TABLE;
            $sql .= " SET progress =  " . $record->progress;
            $sql .= " , total_slides =  " . $total_slides;
            $sql .= " , view_slides  =  " . $view_slides;
            $sql .= " WHERE id =  " . $record->id;
            echo $sql . PHP_EOL;

            $queryMapper->update($sql);

            // $progressMapper->update($record);
        }

        $output->writeln('Fin del proceso de Recalculo');

        return 0;
    }
}