Proyectos de Subversion LeadersLinked - Backend

<?php
declare(strict_types=1);

namespace LeadersLinked\Controller;

use Laminas\Db\Adapter\AdapterInterface;

use Laminas\Mvc\Controller\AbstractActionController;
use Laminas\Log\LoggerInterface;
use Laminas\View\Model\ViewModel;
use Laminas\View\Model\JsonModel;
use LeadersLinked\Form\ExtendUserMicrolearningForm;
use LeadersLinked\Mapper\QueryMapper;
use LeadersLinked\Mapper\UserMapper;
use Laminas\Db\Sql\Expression;
use LeadersLinked\Mapper\MicrolearningCapsuleUserMapper;
use LeadersLinked\Model\MicrolearningCapsuleUser;
use LeadersLinked\Mapper\MicrolearningCapsuleMapper;
use LeadersLinked\Mapper\MicrolearningUserProgressMapper;
use LeadersLinked\Mapper\MicrolearningSlideMapper;
use LeadersLinked\Mapper\MicrolearningUserLogMapper;
use Google\Service\Classroom\Student;
use LeadersLinked\Mapper\MicrolearningExtendUserCompanyMapper;
use LeadersLinked\Mapper\MicrolearningExtendUserFunctionMapper;
use LeadersLinked\Mapper\MicrolearningExtendUserGroupMapper;
use LeadersLinked\Mapper\MicrolearningExtendUserInstitutionMapper;
use LeadersLinked\Mapper\MicrolearningExtendUserProgramMapper;
use LeadersLinked\Mapper\MicrolearningExtendUserPartnerMapper;
use LeadersLinked\Mapper\MicrolearningExtendUserSectorMapper;
use LeadersLinked\Mapper\MicrolearningExtendUserStudentTypeMapper;
use LeadersLinked\Mapper\MicrolearningExtendUserMapper;
use LeadersLinked\Mapper\MicrolearningTopicMapper;
use LeadersLinked\Mapper\DeviceMapper;
use LeadersLinked\Model\Application;
use LeadersLinked\Mapper\MicrolearningUserMapper;
use LeadersLinked\Model\MicrolearningExtendUser;
use LeadersLinked\Form\TopicCapsuleForm;
use LeadersLinked\Mapper\MicrolearningCapsuleCommentMapper;
use Laminas\Hydrator\ArraySerializableHydrator;
use Laminas\Db\ResultSet\HydratingResultSet;
use Laminas\Paginator\Adapter\DbSelect;
use Laminas\Paginator\Paginator;
use LeadersLinked\Model\MicrolearningUserProgress;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Calculation\TextData\Replace;
use LeadersLinked\Form\TopicReportForm;
use LeadersLinked\Model\User;
use LeadersLinked\Mapper\CompanyUserMapper;
use Laminas\Db\Sql\Select;
use LeadersLinked\Form\StudentReportForm;
use LeadersLinked\Library\Functions;



class MicrolearningReportsController extends AbstractActionController
{
    /**
     *
     * @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 LoggerInterface $logger
     * @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;
    }
    
    
    public function indexAction() 
    {
        
        $this->layout()->setTemplate('layout/layout-backend.phtml');
        $viewModel = new ViewModel();
        $viewModel->setTemplate('leaders-linked/microlearning-reports/index.phtml');
        return $viewModel ;
    }
    
   
    public function overviewAction()
    {
        
        
        $currentUserPlugin = $this->plugin('currentUserPlugin');
        $currentUser = $currentUserPlugin->getUser();
        $currentCompany = $currentUserPlugin->getCompany();
        
        
        $request = $this->getRequest();
        if($request->isGet()) {
            $reportsLastWeek = [];
            $reportsCapsuleResume = []; 
            $reportsStudentsTotal = [];
            
            $microlearningExtendUserCompanyMapper = MicrolearningExtendUserCompanyMapper::getInstance($this->adapter);
            $extendCompanies = $microlearningExtendUserCompanyMapper->fetchAllByCompanyId($currentCompany->id);
            
            foreach($extendCompanies as $extendCompany)
            {
                $reportsLastWeek['companies'][$extendCompany->id] = [
                    'name' => $extendCompany->name,
                    'total' => 0,
                    'value' => 0
                ] ;
                
                $reportsCapsuleResume['companies'][$extendCompany->id] = [
                    'name' => $extendCompany->name,
                    'total' => 0,
                    'value' => 0
                ] ;
                
                $reportsStudentsTotal['companies'][$extendCompany->id] = [
                    'name' => $extendCompany->name,
                    'total' => 0,
                ] ;
            }
            

            $microlearningExtendUserFunctionMapper = MicrolearningExtendUserFunctionMapper::getInstance($this->adapter);
            $extendFunctions = $microlearningExtendUserFunctionMapper->fetchAllByCompanyId($currentCompany->id);
            
            foreach($extendFunctions as $extendFunction)
            {
                $reportsLastWeek['functions'][$extendFunction->id] = [
                    'name' => $extendFunction->name,
                    'total' => 0,
                    'value' => 0
                ] ;
                
                $reportsCapsuleResume['functions'][$extendFunction->id] = [
                    'name' => $extendFunction->name,
                    'total' => 0,
                    'value' => 0
                ] ;
                
                $reportsStudentsTotal['functions'][$extendFunction->id] = [
                    'name' => $extendFunction->name,
                    'total' => 0,
                ] ;
            }
            
            $microlearningExtendUserGroupMapper = MicrolearningExtendUserGroupMapper::getInstance($this->adapter);
            $extendGroups = $microlearningExtendUserGroupMapper->fetchAllByCompanyId($currentCompany->id);
            
            foreach($extendGroups as $extendGroup)
            {
                $reportsLastWeek['groups'][$extendGroup->id] = [
                    'name' => $extendGroup->name,
                    'total' => 0,
                    'value' => 0
                ];
                
                $reportsCapsuleResume['groups'][$extendGroup->id] = [
                    'name' => $extendFunction->name,
                    'total' => 0,
                    'value' => 0
                ] ;
                
                $reportsStudentsTotal['groups'][$extendGroup->id] = [
                    'name' => $extendGroup->name,
                    'total' => 0,
                ];
                
                
            }
            
            
            $microlearningExtendUserInstitutionMapper = MicrolearningExtendUserInstitutionMapper::getInstance($this->adapter);
            $extendInstitutions = $microlearningExtendUserInstitutionMapper->fetchAllByCompanyId($currentCompany->id);
            
            foreach($extendInstitutions as $extendInstitution)
            {
                $reportsLastWeek['institutions'][$extendInstitution->id] = [
                    'name' => $extendInstitution->name,
                    'total' => 0,
                    'value' => 0
                ] ;
                
                $reportsCapsuleResume['institutions'][$extendInstitution->id] = [
                    'name' => $extendInstitution->name,
                    'total' => 0,
                    'value' => 0
                ] ;
                
                $reportsStudentsTotal['institutions'][$extendInstitution->id] = [
                    'name' => $extendInstitution->name,
                    'total' => 0,
                ] ;
            }
            
            
            $microlearningExtendUserProgramMapper = MicrolearningExtendUserProgramMapper::getInstance($this->adapter);
            $extendPrograms = $microlearningExtendUserProgramMapper->fetchAllByCompanyId($currentCompany->id);
            
            foreach($extendPrograms as $extendProgram)
            {
                $reportsLastWeek['programs'][$extendProgram->id] = [
                    'name' => $extendProgram->name,
                    'total' => 0,
                    'value' => 0
                ] ;
                
                $reportsCapsuleResume['programs'][$extendProgram->id] = [
                    'name' => $extendProgram->name,
                    'total' => 0,
                    'value' => 0
                ] ;
                
                $reportsStudentsTotal['programs'][$extendProgram->id] = [
                    'name' => $extendProgram->name,
                    'total' => 0,

                ] ;
            }
            
            
            $microlearningExtendUserPartnerMapper = MicrolearningExtendUserPartnerMapper::getInstance($this->adapter);
            $extendPartners = $microlearningExtendUserPartnerMapper->fetchAllByCompanyId($currentCompany->id);
            
            foreach($extendPartners as $extendPartner)
            {
                $reportsLastWeek['partners'][$extendPartner->id] = [
                    'name' => $extendPartner->name,
                    'total' => 0,
                    'value' => 0
                ] ;
                
                $reportsCapsuleResume['partners'][$extendPartner->id] = [
                    'name' => $extendPartner->name,
                    'total' => 0,
                    'value' => 0
                ] ;
                
                $reportsStudentsTotal['partners'][$extendPartner->id] = [
                    'name' => $extendPartner->name,
                    'total' => 0,
                ] ;
            }
            
            
            $microlearningExtendUserSectorMapper = MicrolearningExtendUserSectorMapper::getInstance($this->adapter);
            $extendSectors = $microlearningExtendUserSectorMapper->fetchAllByCompanyId($currentCompany->id);
            
            foreach($extendSectors as $extendSector)
            {
                $reportsLastWeek['sectors'][$extendSector->id] = [
                    'name' => $extendSector->name,
                    'total' => 0,
                    'value' => 0
                ] ;
                
                $reportsCapsuleResume['sectors'][$extendSector->id] = [
                    'name' => $extendSector->name,
                    'total' => 0,
                    'value' => 0
                ] ;
                
                $reportsStudentsTotal['sectors'][$extendSector->id] = [
                    'name' => $extendSector->name,
                    'total' => 0,
                ] ;
            }
            
            $microlearningExtendUserStudentTypeMapper = MicrolearningExtendUserStudentTypeMapper::getInstance($this->adapter);
            $extendStudentTypes = $microlearningExtendUserStudentTypeMapper->fetchAllByCompanyId($currentCompany->id);
            
            foreach($extendStudentTypes as $extendStudentType)
            {
                $reportsLastWeek['student_types'][$extendStudentType->id] = [
                    'name' => $extendStudentType->name,
                    'total' => 0,
                    'value' => 0
                ] ;
                
                $reportsCapsuleResume['student_types'][$extendStudentType->id] = [
                    'name' => $extendStudentType->name,
                    'total' => 0,
                    'value' => 0
                ] ;
                
                $reportsStudentsTotal['student_types'][$extendStudentType->id] = [
                    'name' => $extendStudentType->name,
                    'total' => 0,
                ] ;
            }
            
            $microlearningUserProgressMapper = MicrolearningUserProgressMapper::getInstance($this->adapter);
            
            $minLastWeek = date('Y-m-d', strtotime(' -180 days'));
            $maxLastWeek = date('Y-m-d'); 
            $microlearningUserLogMapper = MicrolearningUserLogMapper::getInstance($this->adapter);
            $companyUsersLogWithActivityLastWeek = $microlearningUserLogMapper->fetchAllUserIdsLastWeekByCompanyId($currentCompany->id, $minLastWeek, $maxLastWeek);
            
            
            $date = date('Y-m-d');
            
            $microlearningCapsuleUserMapper = MicrolearningCapsuleUserMapper::getInstance($this->adapter);
            $user_ids = $microlearningCapsuleUserMapper->fetchAllDistinctUserIdActiveByCompanyIdAndDate($currentCompany->id, $date); 
            
            $studentsTotal = count($user_ids);
            $studentsStarted = 0;

            
            $userIdsWithAllCapsulesCompleted = [];

            
            foreach($user_ids as $user_id)
            {
                $countSlides = $microlearningUserProgressMapper->fetchCountAllSlideCompletedByCompanyIdAndUserId($currentCompany->id, $user_id); 
                if($countSlides) {
                    $studentsStarted++;
                } 
                
                $incompleted = false;
                
                $capsuleUsers = $microlearningCapsuleUserMapper->fetchAllActiveByUserId($user_id);
                foreach($capsuleUsers as $capsuleUser)
                {
                    $userProgress = $microlearningUserProgressMapper->fetchOneByUseridAndCapsuleId($capsuleUser->user_id, $capsuleUser->capsule_id);
                    if($userProgress) {
                        if(!$userProgress->completed) {
                            $incompleted = true;
                            break;
                        }
                    }  else {
                        $incompleted = true;
                        break;
                    } 
                    
                    
                    
                }
                
                if(!$incompleted) {
                    array_push($userIdsWithAllCapsulesCompleted, $user_id);
                } 
            }
            
            
            $studentsWithoutStarting =  $studentsTotal - $studentsStarted;
            $reportsLastWeek['general'] = ['total' => $studentsTotal, 'value' => count($companyUsersLogWithActivityLastWeek) ];
        
            $reportsCapsuleResume['general'] = ['total' => $studentsTotal, 'value' => count($userIdsWithAllCapsulesCompleted) ];
            
            
            $microlearningExtendUserMapper = MicrolearningExtendUserMapper::getInstance($this->adapter);
            $records = $microlearningExtendUserMapper->fetchAllByCompanyId($currentCompany->id);
            foreach($records as $record)
            {

                if($record->extend_company_id) { 
                    $reportsLastWeek['companies'][$record->extend_company_id]['total'] += 1;
                    $reportsStudentsTotal['companies'][$record->extend_company_id]['total'] += 1;
                    
                    if(in_array($record->user_id, $companyUsersLogWithActivityLastWeek )) {
                        $reportsLastWeek['companies'][$record->extend_company_id]['value'] += 1;
                    }
                    
                    $reportsCapsuleResume['companies'][$record->extend_company_id]['total'] += 1;
                    
                    if(in_array($record->user_id, $userIdsWithAllCapsulesCompleted )) {
                        $reportsCapsuleResume['companies'][$record->extend_company_id]['value'] += 1;
                    }
                    
                }
                
                if($record->extend_function_id) {
                    $reportsLastWeek['functions'][$record->extend_function_id]['total'] += 1;
                    $reportsStudentsTotal['functions'][$record->extend_function_id]['total'] += 1;
                    
                    if(in_array($record->user_id, $companyUsersLogWithActivityLastWeek )) {
                        $reportsLastWeek['functions'][$record->extend_function_id]['value'] += 1;
                    }
                    
                    $reportsCapsuleResume['functions'][$record->extend_function_id]['total'] += 1;
                    if(in_array($record->user_id, $userIdsWithAllCapsulesCompleted )) {
                        $reportsCapsuleResume['functions'][$record->extend_function_id]['value'] += 1;
                    }
                }
                
                if($record->extend_group_id) {
                    $reportsLastWeek['groups'][$record->extend_group_id]['total'] += 1;
                    $reportsStudentsTotal['groups'][$record->extend_group_id]['total'] += 1;
                    if(in_array($record->user_id, $companyUsersLogWithActivityLastWeek )) {
                        $reportsLastWeek['groups'][$record->extend_group_id]['value'] += 1;
                    }
                    
                    $reportsCapsuleResume['groups'][$record->extend_group_id]['total'] += 1;
                    if(in_array($record->user_id, $userIdsWithAllCapsulesCompleted )) {
                        $reportsCapsuleResume['groups'][$record->extend_group_id]['value'] += 1;
                    }
                }
                
                if($record->extend_institution_id) {
                    $reportsLastWeek['institutions'][$record->extend_institution_id]['total'] += 1;
                    $reportsStudentsTotal['institutions'][$record->extend_institution_id]['total'] += 1;
                    if(in_array($record->user_id, $companyUsersLogWithActivityLastWeek )) {
                        $reportsLastWeek['institutions'][$record->extend_institution_id]['value'] += 1;
                    }
                    
                    $reportsCapsuleResume['institutions'][$record->extend_institution_id]['total'] += 1;
                    if(in_array($record->user_id, $userIdsWithAllCapsulesCompleted )) {
                        $reportsCapsuleResume['institutions'][$record->extend_institution_id]['value'] += 1;
                    }
                }
                
                if($record->extend_program_id) {
                    $reportsLastWeek['programs'][$record->extend_program_id]['total'] += 1;
                    $reportsStudentsTotal['programs'][$record->extend_program_id]['total'] += 1;
                    if(in_array($record->user_id, $companyUsersLogWithActivityLastWeek )) {
                        $reportsLastWeek['programs'][$record->extend_program_id]['value'] += 1;
                    }
                    
                    $reportsCapsuleResume['programs'][$record->extend_program_id]['total'] += 1;
                    if(in_array($record->user_id, $userIdsWithAllCapsulesCompleted )) {
                        $reportsCapsuleResume['programs'][$record->extend_program_id]['value'] += 1;
                    }
                }
                
                if($record->extend_partner_id) {
                    $reportsLastWeek['partners'][$record->extend_partner_id]['total'] += 1;
                    $reportsStudentsTotal['partners'][$record->extend_partner_id]['total'] += 1;
                    if(in_array($record->user_id, $companyUsersLogWithActivityLastWeek )) {
                        $reportsLastWeek['partners'][$record->extend_partner_id]['value'] += 1;
                    }
                    
                    $reportsCapsuleResume['partners'][$record->extend_partner_id]['total'] += 1;
                    if(in_array($record->user_id, $userIdsWithAllCapsulesCompleted )) {
                        $reportsCapsuleResume['partners'][$record->extend_partner_id]['value'] += 1;
                    }
                }
                
                if($record->extend_sector_id) {
                    $reportsLastWeek['sectors'][$record->extend_sector_id]['total'] += 1;
                    $reportsStudentsTotal['sectors'][$record->extend_sector_id]['total'] += 1;
                    if(in_array($record->user_id, $companyUsersLogWithActivityLastWeek )) {
                        $reportsLastWeek['sectors'][$record->extend_sector_id]['value'] += 1;
                    }
                    
                    $reportsCapsuleResume['sectors'][$record->extend_sector_id]['total'] += 1;
                    if(in_array($record->user_id, $userIdsWithAllCapsulesCompleted )) {
                        $reportsCapsuleResume['sectors'][$record->extend_sector_id]['value'] += 1;
                    }
                }
                
                if($record->extend_student_type_id) {
                    $reportsLastWeek['student_types'][$record->extend_student_type_id]['total'] += 1;
                    $reportsStudentsTotal['student_types'][$record->extend_student_type_id]['total'] += 1;
                    if(in_array($record->user_id, $companyUsersLogWithActivityLastWeek )) {
                        $reportsLastWeek['student_types'][$record->extend_student_type_id]['value'] += 1;
                    }
                    
                    $reportsCapsuleResume['student_types'][$record->extend_student_type_id]['total'] += 1;
                    if(in_array($record->user_id, $userIdsWithAllCapsulesCompleted )) {
                        $reportsCapsuleResume['student_types'][$record->extend_student_type_id]['value'] += 1;
                    }
                }
            }
            
          

            
            $this->layout()->setTemplate('layout/layout-backend.phtml');
            $viewModel = new ViewModel();
            $viewModel->setTemplate('leaders-linked/microlearning-reports/overview.phtml');
            $viewModel->setVariables([
                'reportsLastWeek'       => $reportsLastWeek,
                'reportsCapsuleResume'  => $reportsCapsuleResume,
                'reportsStudentsTotal'  => $reportsStudentsTotal,
                'reportStudents' => [
                    'total' => $studentsTotal,
                    'started' => $studentsStarted,
                    'withoutStarting' => $studentsWithoutStarting,
                ]
                
            ]); 
            return $viewModel ;
        }
        
        return new JsonModel([
           'success' => false,
           'data' => 'ERROR_METHOD_NOT_ALLOWED'
        ]);
    }

    
    
    public function progressAction()
    {
        
        
        $currentUserPlugin = $this->plugin('currentUserPlugin');
        $currentUser = $currentUserPlugin->getUser();
        $currentCompany = $currentUserPlugin->getCompany();
        
        
        $request = $this->getRequest();
        if($request->isGet()) {
            
            
            $headers  = $request->getHeaders();
            
            $isJson = false;
            if($headers->has('Accept')) {
                $accept = $headers->get('Accept');
                
                $prioritized = $accept->getPrioritized();
                
                foreach($prioritized as $key => $value) {
                    $raw = trim($value->getRaw());
                    
                    if(!$isJson) {
                        $isJson = strpos($raw, 'json');
                    }
                    
                }
            }
            

            
            if($isJson) {
         
                $queryMapper = QueryMapper::getInstance($this->adapter);
                
                $selectUsers = $queryMapper->getSql()->select();
                $selectUsers->columns(['user_id' => new Expression('DISTINCT(user_id)')]);
                $selectUsers->from(MicrolearningCapsuleUserMapper::_TABLE);
                $selectUsers->where->equalTo('company_id', $currentCompany->id);
                

                
                
                
                $select = $queryMapper->getSql()->select();
                $select->columns(['id' , 'uuid', 'first_name', 'last_name', 'email']);
                $select->from([ 'u' => UserMapper::_TABLE]);
                
                
                
                $select->where->in('u.id', $selectUsers);
                $select->where->equalTo('u.status', User::STATUS_ACTIVE);
   
                $select->order(['u.first_name', 'u.last_name']);
                
               // echo $select->getSqlString($this->adapter->platform); exit;
     
                
                

                $microlearningProgressMapper = MicrolearningUserProgressMapper::getInstance($this->adapter);
                $microlearningUserLogMapper = MicrolearningUserLogMapper::getInstance($this->adapter);
                
                $microlearningCapsuleUserMapper = MicrolearningCapsuleUserMapper::getInstance($this->adapter);
                
               // $microlearningSlideMapper = MicrolearningSlideMapper::getInstance($this->adapter);
                
                
                
                $students = [];
                
                $records = $queryMapper->fetchAll($select);
                
                
                foreach($records as $record)
                {
         

                    $capsules               = $microlearningCapsuleUserMapper->fetchAllActiveByUserId($record['id']);
                    
                    

                    $totalCapsules          = count($capsules);
                    $totalCapsulesStarted   = 0;
                    $totalCapsulesCompleted = 0;
                    
                    
        
                    
                    

                    
                    foreach($capsules as $capsule)
                    {
                        $userProgress = $microlearningProgressMapper->fetchOneByUseridAndCapsuleId($capsule->user_id, $capsule->capsule_id);
                       
      
                        if($userProgress) {
                            $totalCapsulesStarted++;
                            if($userProgress->completed) {
                                $totalCapsulesCompleted++;
                            }
                        }
                       
                        
                        
                    }
                    
                    
                    
      
                    if($totalCapsulesCompleted > 0 && $totalCapsules > 0) {
                        $total_capsules_percentaje = intval ( ($totalCapsulesCompleted * 100 ) / $totalCapsules);
                    }  else {
                        $total_capsules_percentaje = 0;
                    }
                    
  
                 
                    $student = [
                        'uuid' => $record['uuid'],
                        'first_name' => $record['first_name'],
                        'last_name' => $record['last_name'],
                        'email' => $record['email'],
                        
                        'total_capsules' => $totalCapsules,
                        'total_capsules_incomplete' => ( $totalCapsules - $totalCapsulesCompleted ),
                        'total_capsules_started' => $totalCapsulesStarted,
                        'total_capsules_completed' => $totalCapsulesCompleted,
                        'total_capsules_percentaje' => $total_capsules_percentaje,

                        'first_date' => '',
                        'first_time' => '',
                        'last_date' => '',
                        'last_time' => '',
                    ];
    
                    $first_added_on = $microlearningUserLogMapper->fetchFirstDateByCompanyIdAndUserId($currentCompany->id, $record['id']);
                    if($first_added_on) {
                        $dt = \DateTime::createFromFormat('Y-m-d H:i:s', $first_added_on);
                        if($dt) {
                            $student['first_date'] = $dt->format('d/m/Y');
                            $student['first_time'] = $dt->format('h:i a');
                        }
                    }
                    
                    $last_added_on  = $microlearningUserLogMapper->fetchLastDateByCompanyIdAndUserId($currentCompany->id, $record['id']);
                    if($last_added_on) {
                        $dt = \DateTime::createFromFormat('Y-m-d H:i:s', $last_added_on);
                        if($dt) {
                            $student['last_date'] = $dt->format('d/m/Y');
                            $student['last_time'] = $dt->format('h:i a');
                        }
                    }
       
                    array_push($students, $student);
                   
                    
                    
                }

                return new JsonModel([
                    'success' => true,
                    'data' => $students
                    
                ]);
                
            } else {
                
                
                
                // $form = new ExtendUserMicrolearningForm($this->adapter, $currentCompany->id);
                $form = new TopicCapsuleForm($this->adapter, $currentCompany->id, $topic_id = 0);
                
                
                $this->layout()->setTemplate('layout/layout-backend.phtml');
                $viewModel = new ViewModel();
                $viewModel->setTemplate('leaders-linked/microlearning-reports/progress-for-capsule.phtml');
                $viewModel->setVariables([
                    'form' => $form
                ]);
                return $viewModel ;
            }
        }
        
        return new JsonModel([
            'success' => false,
            'data' => 'ERROR_METHOD_NOT_ALLOWED'
        ]);
    }
    
        
    public function devicesAndAccessAction()
    {
        
        
        $currentUserPlugin = $this->plugin('currentUserPlugin');
        $currentUser = $currentUserPlugin->getUser();
        $currentCompany = $currentUserPlugin->getCompany();
        
        
        $request = $this->getRequest();
        if($request->isGet()) {
            
            
            $headers  = $request->getHeaders();
            
            $isJson = false;
            if($headers->has('Accept')) {
                $accept = $headers->get('Accept');
                
                $prioritized = $accept->getPrioritized();
                
                foreach($prioritized as $key => $value) {
                    $raw = trim($value->getRaw());
                    
                    if(!$isJson) {
                        $isJson = strpos($raw, 'json');
                    }
                    
                }
            }
            
            if($isJson) {
                $acl = $this->getEvent()->getViewModel()->getVariable('acl');
                $allowDownload = $acl->isAllowed($currentUser->usertype_id, 'microlearning/reports/progress-for-capsule/excel');
                
                $company_id = Functions::sanitizeFilterString($this->params()->fromQuery('company_id', ''));
                $function_id = Functions::sanitizeFilterString($this->params()->fromQuery('function_id', ''));
                $group_id = Functions::sanitizeFilterString($this->params()->fromQuery('group_id', ''));
                $institution_id = Functions::sanitizeFilterString($this->params()->fromQuery('institution_id', ''));
                $program_id = Functions::sanitizeFilterString($this->params()->fromQuery('program_id', ''));
                $partner_id = Functions::sanitizeFilterString($this->params()->fromQuery('partner_id', ''));
                $sector_id = Functions::sanitizeFilterString($this->params()->fromQuery('sector_id', ''));
                $student_type_id = Functions::sanitizeFilterString($this->params()->fromQuery('student_type_id', ''));
 
                
                if($company_id) {
                    $extendUserCompanyMapper = MicrolearningExtendUserCompanyMapper::getInstance($this->adapter);
                    $extendUserCompany = $extendUserCompanyMapper->fetchOneByUuid($company_id);
                    
                    if($extendUserCompany) {
                        $company_id = $extendUserCompany->id;
                    } else {
                        $company_id = 0;
                    }
                }
                
                if($function_id) {
                    $extendUserFunctionMapper = MicrolearningExtendUserFunctionMapper::getInstance($this->adapter);
                    $extendUserFunction = $extendUserFunctionMapper->fetchOneByUuid($function_id);
                    
                    if($extendUserFunction) {
                        $function_id = $extendUserFunction->id;
                    } else {
                        $function_id = 0;
                    }
                }
                
                if($group_id) {
                    $extendUserGroupMapper = MicrolearningExtendUserGroupMapper::getInstance($this->adapter);
                    $extendUserGroup = $extendUserGroupMapper->fetchOneByUuid($group_id);
                    
                    if($extendUserGroup) {
                        $group_id = $extendUserGroup->id;        
                    } else {
                        $group_id = 0;
                    } 
                }
                
                if($institution_id) {
                    $extendUserInstitutionMapper = MicrolearningExtendUserInstitutionMapper::getInstance($this->adapter);
                    $extendUserInstitution = $extendUserInstitutionMapper->fetchOneByUuid($institution_id);
                    if($extendUserInstitution) {
                        $institution_id = $extendUserInstitution->id;
                    } else {
                        $institution_id = 0;
                    }
                }
                
                if($program_id) {
                    $extendUserProgramMapper = MicrolearningExtendUserProgramMapper::getInstance($this->adapter);
                    $extendUserProgram = $extendUserProgramMapper->fetchOneByUuid($program_id);
                   
                    if($extendUserProgram) {
                        $program_id = $extendUserProgram->id;
                    } else {
                        $program_id = 0;
                    }
                    
                    
                }
                
                if($partner_id) {
                    $extendUserPartnerMapper = MicrolearningExtendUserPartnerMapper::getInstance($this->adapter);
                    $extendUserPartner = $extendUserPartnerMapper->fetchOneByUuid($partner_id);
                    if($extendUserPartner) {
                        $partner_id = $extendUserPartner;
                    } else {
                        $partner_id = 0;
                    }
                }
                
                if($sector_id) {
                    $extendUserSectorMapper = MicrolearningExtendUserSectorMapper::getInstance($this->adapter);
                    $extendUserSector = $extendUserSectorMapper->fetchOneByUuid($sector_id);
                    if($extendUserSector) {
                        $sector_id = $extendUserSector->id;
                    } else {
                        $sector_id = 0;
                    }
                }
                
                if($student_type_id) {
                    $extendUserStudentTypeMapper = MicrolearningExtendUserStudentTypeMapper::getInstance($this->adapter);
                    $extendUserStudentType = $extendUserStudentTypeMapper->fetchOneByUuid($student_type_id);
                   
                    if($extendUserStudentType) {
                        $student_type_id = $extendUserStudentType->id;
                    } else {
                        $student_type_id = 0;
                    }
                }
                
                
                
                $paramsDownload = [
                    'company_id' => $company_id,
                    'function_id' => $function_id,
                    'group_id' => $group_id,
                    'institution_id' => $institution_id,
                    'program_id' => $program_id,
                    'partner_id' => $partner_id,
                    'sector_id' =>  $sector_id,
                    'student_type_id' => $student_type_id
                ];
                
                
                /*
                echo 'company_id 1 =  '. $company_id . PHP_EOL;
                echo 'function_id 1 =  '. $function_id . PHP_EOL;
                echo 'group_id 1 =  '. $group_id . PHP_EOL;
                echo 'institution_id 1 =  '. $institution_id . PHP_EOL;
                echo 'program_id 1 =  '. $program_id . PHP_EOL;
                echo 'partner_id 1 =  '. $partner_id . PHP_EOL;
                echo 'sector_id 1 =  '. $sector_id . PHP_EOL;
                echo 'student_type_id 1 =  '. $student_type_id . PHP_EOL;
                exit;
                */
                
                
                $date = date('Y-m-d');
                $deviceMapper = DeviceMapper::getInstance($this->adapter);
                
                
                $queryMapper = QueryMapper::getInstance($this->adapter);
                
                $selectCapsuleUser = $queryMapper->getSql()->select();
                $selectCapsuleUser->columns(['user_id' => new Expression('DISTINCT(user_id)')  ]);
                $selectCapsuleUser->from(MicrolearningCapsuleUserMapper::_TABLE);
                $selectCapsuleUser->where->equalTo('company_id', $currentCompany->id);
                $selectCapsuleUser->where->nest->equalTo('access', MicrolearningCapsuleUser::ACCESS_UNLIMITED)->or->nest()
                ->equalTo('access', MicrolearningCapsuleUser::ACCESS_PAY_PERIOD)
                ->and->lessThanOrEqualTo(new Expression('DATE(paid_from)'), $date)
                ->and->greaterThanOrEqualTo(new Expression('DATE(paid_to)'), $date )->unnest()->unnest();
                
                //echo $selectCapsuleUser->getSqlString($this->adapter->platform); exit;
                
                $select = $queryMapper->getSql()->select();
                $select->columns(['id' , 'uuid', 'first_name', 'last_name', 'email']);
                $select->from([ 'u' => UserMapper::_TABLE]);
                if( $company_id || $function_id  || $group_id || $institution_id || $program_id || $partner_id ||  $sector_id || $student_type_id ) {
                    
                    $select->join(['e' => MicrolearningExtendUserMapper::_TABLE], 'e.user_id = u.id', []);
                    
                }
                
                $select->where->in('u.id', $selectCapsuleUser);
                if($company_id) {
                    $select->where->equalTo('e.extend_company_id', $company_id);
                }
                
                if($function_id) {
                    $select->where->equalTo('e.extend_function_id', $function_id);
                    
                }  
                if($group_id) {
                    $select->where->equalTo('e.extend_group_id', $group_id);
                } 
                
                if($institution_id) {
                    $select->where->equalTo('e.extend_institution_id', $institution_id);
                }
                
                if($program_id) {
                    $select->where->equalTo('e.extend_program_id', $program_id);
                }
                
                if($partner_id) {
                    $select->where->equalTo('e.extend_partner_id', $partner_id);
                }
                
                if($sector_id) {
                    $select->where->equalTo('e.extend_sector_id', $sector_id);
                }
                
                if($student_type_id) {
                    $select->where->equalTo('e.extend_student_type_id', $student_type_id);
                }
                
                $select->where->equalTo('u.status', User::STATUS_ACTIVE);
                $select->order(['u.first_name', 'u.last_name']);
       
                
                
                $items = [];
                $records = $queryMapper->fetchAll($select);
                foreach($records as $record)
                {
                    $item = [
                        'uuid' => $record['uuid'],
                        'first_name' => ucwords(strtolower($record['first_name'])),
                        'last_name' => ucwords(strtolower($record['last_name'])),
                        'email' => strtolower($record['email']),
                        'brand' => '',
                        'manufacturer' => '',
                        'model' => '',
                        'platform' => '',
                        'version' => '',
                        'token' => 'LABEL_NO',
                        'updated_on' => '',
                    ];
                    
                    $device = $deviceMapper->fetchOneByUserId($record['id']);
                    if($device) {
                        $dt = \DateTime::createFromFormat('Y-m-d H:i:s', $device->updated_on);
                        $updated_on = $dt->format('d/m/Y h:i a');
                        
                        $item['brand'] = $device->brand;
                        $item['manufacturer'] = $device->manufacturer;
                        $item['model'] = $device->model;
                        $item['platform'] = $device->platform;
                        $item['version'] = $device->version;
                        $item['token'] = $device->token ? 'LABEL_YES' : 'LABEL_NO';
                        $item['updated_on'] = $updated_on;

                    }
             
                    array_push($items, $item);
                    
                    
                }
                
                $link_download = '';
                if($allowDownload) {

                    $link_download = $this->url()->fromRoute('microlearning/reports/devices-and-access/excel', [], ['query' => $paramsDownload ]);
                }
                
                
                
             
                
                
                
                return new JsonModel([
                    'success' => true,
                    'data' => [
                        'link_download' => $link_download,
                        'items' => $items
                    ],
                    
                ]);
                
            } else {
                
                
                
                $form = new ExtendUserMicrolearningForm($this->adapter, $currentCompany->id);
                
                
                $this->layout()->setTemplate('layout/layout-backend.phtml');
                $viewModel = new ViewModel();
                $viewModel->setTemplate('leaders-linked/microlearning-reports/devices-and-access.phtml');
                $viewModel->setVariables([
                    'form' => $form
                ]);
                return $viewModel ;
            }
        }
        
        return new JsonModel([
            'success' => false,
            'data' => 'ERROR_METHOD_NOT_ALLOWED'
        ]);
    }
    
    
    public function devicesAndAccessExcelAction()
    {
        $currentUserPlugin = $this->plugin('currentUserPlugin');
        $currentUser = $currentUserPlugin->getUser();
        $currentCompany = $currentUserPlugin->getCompany();
        
        $request = $this->getRequest();
        
        if($request->isGet())
        {
            
            $company_id = Functions::sanitizeFilterString($this->params()->fromQuery('company_id', ''));
            $function_id = Functions::sanitizeFilterString($this->params()->fromQuery('function_id', ''));
            $group_id = Functions::sanitizeFilterString($this->params()->fromQuery('group_id', ''));
            $institution_id = Functions::sanitizeFilterString($this->params()->fromQuery('institution_id', ''));
            $program_id = Functions::sanitizeFilterString($this->params()->fromQuery('program_id', ''));
            $partner_id = Functions::sanitizeFilterString($this->params()->fromQuery('partner_id', ''));
            $sector_id = Functions::sanitizeFilterString($this->params()->fromQuery('sector_id', ''));
            $student_type_id = Functions::sanitizeFilterString($this->params()->fromQuery('student_type_id', ''));
            
            
            if($company_id) {
                $extendUserCompanyMapper = MicrolearningExtendUserCompanyMapper::getInstance($this->adapter);
                $extendUserCompany = $extendUserCompanyMapper->fetchOneByUuid($company_id);
                
                if($extendUserCompany) {
                    $company_id = $extendUserCompany->id;
                } else {
                    $company_id = 0;
                }
            }
            
            if($function_id) {
                $extendUserFunctionMapper = MicrolearningExtendUserFunctionMapper::getInstance($this->adapter);
                $extendUserFunction = $extendUserFunctionMapper->fetchOneByUuid($function_id);
                
                if($extendUserFunction) {
                    $function_id = $extendUserFunction->id;
                } else {
                    $function_id = 0;
                }
            }
            
            if($group_id) {
                $extendUserGroupMapper = MicrolearningExtendUserGroupMapper::getInstance($this->adapter);
                $extendUserGroup = $extendUserGroupMapper->fetchOneByUuid($group_id);
                
                if($extendUserGroup) {
                    $group_id = $extendUserGroup->id;
                } else {
                    $group_id = 0;
                }
            }
            
            if($institution_id) {
                $extendUserInstitutionMapper = MicrolearningExtendUserInstitutionMapper::getInstance($this->adapter);
                $extendUserInstitution = $extendUserInstitutionMapper->fetchOneByUuid($institution_id);
                if($extendUserInstitution) {
                    $institution_id = $extendUserInstitution->id;
                } else {
                    $institution_id = 0;
                }
            }
            
            if($program_id) {
                $extendUserProgramMapper = MicrolearningExtendUserProgramMapper::getInstance($this->adapter);
                $extendUserProgram = $extendUserProgramMapper->fetchOneByUuid($program_id);
                
                if($extendUserProgram) {
                    $program_id = $extendUserProgram->id;
                } else {
                    $program_id = 0;
                }
                
                
            }
            
            if($partner_id) {
                $extendUserPartnerMapper = MicrolearningExtendUserPartnerMapper::getInstance($this->adapter);
                $extendUserPartner = $extendUserPartnerMapper->fetchOneByUuid($partner_id);
                if($extendUserPartner) {
                    $partner_id = $extendUserPartner;
                } else {
                    $partner_id = 0;
                }
            }
            
            if($sector_id) {
                $extendUserSectorMapper = MicrolearningExtendUserSectorMapper::getInstance($this->adapter);
                $extendUserSector = $extendUserSectorMapper->fetchOneByUuid($sector_id);
                if($extendUserSector) {
                    $sector_id = $extendUserSector->id;
                } else {
                    $sector_id = 0;
                }
            }
            
            if($student_type_id) {
                $extendUserStudentTypeMapper = MicrolearningExtendUserStudentTypeMapper::getInstance($this->adapter);
                $extendUserStudentType = $extendUserStudentTypeMapper->fetchOneByUuid($student_type_id);
                
                if($extendUserStudentType) {
                    $student_type_id = $extendUserStudentType->id;
                } else {
                    $student_type_id = 0;
                }
            }
            
            
            
            $paramsDownload = [
                'company_id' => $company_id,
                'function_id' => $function_id,
                'group_id' => $group_id,
                'institution_id' => $institution_id,
                'program_id' => $program_id,
                'partner_id' => $partner_id,
                'sector_id' =>  $sector_id,
                'student_type_id' => $student_type_id
            ];
            
            
            /*
             echo 'company_id 1 =  '. $company_id . PHP_EOL;
             echo 'function_id 1 =  '. $function_id . PHP_EOL;
             echo 'group_id 1 =  '. $group_id . PHP_EOL;
             echo 'institution_id 1 =  '. $institution_id . PHP_EOL;
             echo 'program_id 1 =  '. $program_id . PHP_EOL;
             echo 'partner_id 1 =  '. $partner_id . PHP_EOL;
             echo 'sector_id 1 =  '. $sector_id . PHP_EOL;
             echo 'student_type_id 1 =  '. $student_type_id . PHP_EOL;
             exit;
             */
            
            
            $date = date('Y-m-d');
            $deviceMapper = DeviceMapper::getInstance($this->adapter);
            
            
            $queryMapper = QueryMapper::getInstance($this->adapter);
            
            $selectCapsuleUser = $queryMapper->getSql()->select();
            $selectCapsuleUser->columns(['user_id' => new Expression('DISTINCT(user_id)')  ]);
            $selectCapsuleUser->from(MicrolearningCapsuleUserMapper::_TABLE);
            $selectCapsuleUser->where->equalTo('company_id', $currentCompany->id);
            $selectCapsuleUser->where->nest->equalTo('access', MicrolearningCapsuleUser::ACCESS_UNLIMITED)->or->nest()
            ->equalTo('access', MicrolearningCapsuleUser::ACCESS_PAY_PERIOD)
            ->and->lessThanOrEqualTo(new Expression('DATE(paid_from)'), $date)
            ->and->greaterThanOrEqualTo(new Expression('DATE(paid_to)'), $date )->unnest()->unnest();
            
            //echo $selectCapsuleUser->getSqlString($this->adapter->platform); exit;
            
            $select = $queryMapper->getSql()->select();
            $select->columns(['id' , 'uuid', 'first_name', 'last_name', 'email']);
            $select->from([ 'u' => UserMapper::_TABLE]);
            if( $company_id || $function_id  || $group_id || $institution_id || $program_id || $partner_id ||  $sector_id || $student_type_id ) {
                
                $select->join(['e' => MicrolearningExtendUserMapper::_TABLE], 'e.user_id = u.id', []);
                
            }
            
            $select->where->in('u.id', $selectCapsuleUser);
            if($company_id) {
                $select->where->equalTo('e.extend_company_id', $company_id);
            }
            
            if($function_id) {
                $select->where->equalTo('e.extend_function_id', $function_id);
                
            }
            if($group_id) {
                $select->where->equalTo('e.extend_group_id', $group_id);
            }
            
            if($institution_id) {
                $select->where->equalTo('e.extend_institution_id', $institution_id);
            }
            
            if($program_id) {
                $select->where->equalTo('e.extend_program_id', $program_id);
            }
            
            if($partner_id) {
                $select->where->equalTo('e.extend_partner_id', $partner_id);
            }
            
            if($sector_id) {
                $select->where->equalTo('e.extend_sector_id', $sector_id);
            }
            
            if($student_type_id) {
                $select->where->equalTo('e.extend_student_type_id', $student_type_id);
            }
            
            $select->where->equalTo('u.status', User::STATUS_ACTIVE);
            $select->order(['u.first_name', 'u.last_name']);
            
            
            
            $items = [];
            $records = $queryMapper->fetchAll($select);
            foreach($records as $record)
            {
                $item = [
                    'uuid' => $record['uuid'],
                    'first_name' => ucwords(strtolower($record['first_name'])),
                    'last_name' => ucwords(strtolower($record['last_name'])),
                    'email' => strtolower($record['email']),
                    'brand' => '',
                    'manufacturer' => '',
                    'model' => '',
                    'platform' => '',
                    'version' => '',
                    'token' => 'LABEL_NO',
                    'updated_on' => '',
                ];
                
                $device = $deviceMapper->fetchOneByUserId($record['id']);
                if($device) {
                    $dt = \DateTime::createFromFormat('Y-m-d H:i:s', $device->updated_on);
                    $updated_on = $dt->format('d/m/Y h:i a');
                    
                    $item['brand'] = $device->brand;
                    $item['manufacturer'] = $device->manufacturer;
                    $item['model'] = $device->model;
                    $item['platform'] = $device->platform;
                    $item['version'] = $device->version;
                    $item['token'] = $device->token ? 'Si' : 'No';
                    $item['updated_on'] = $updated_on;
                    
                }
                
                array_push($items, $item);
                
                
            }
            
            $records = $items;
            
            $spreadsheet = new Spreadsheet();
            $spreadsheet->getProperties()->setTitle("Dispositivos y Accesos");
            
            
            
            $spreadsheet->setActiveSheetIndex(0);
            $spreadsheet->getActiveSheet()->SetCellValue('A1', 'Fecha:');
            $spreadsheet->getActiveSheet()->SetCellValue('A2', date('d/m/Y h:i a'));
            
            $spreadsheet->getActiveSheet()->SetCellValue('A4', 'Nombre');
            $spreadsheet->getActiveSheet()->SetCellValue('B4', 'Apellido');
            $spreadsheet->getActiveSheet()->SetCellValue('C4', 'Correo electrónico');
            $spreadsheet->getActiveSheet()->SetCellValue('D4', 'Marca');
            $spreadsheet->getActiveSheet()->SetCellValue('E4', 'Fabricante');
            $spreadsheet->getActiveSheet()->SetCellValue('F4', 'Modelo');
            $spreadsheet->getActiveSheet()->SetCellValue('G4', 'Plataforma');
            $spreadsheet->getActiveSheet()->SetCellValue('H4', 'Versión');
            $spreadsheet->getActiveSheet()->SetCellValue('I4', 'Token push');
            $spreadsheet->getActiveSheet()->SetCellValue('J4', 'Ultima vez');
            
            
            $i = 5;
            foreach($records as $record)
            {
                $spreadsheet->getActiveSheet()->SetCellValue('A' . $i, ucwords(strtolower(trim($record['first_name']))));
                $spreadsheet->getActiveSheet()->SetCellValue('B' . $i, ucwords(strtolower(trim($record['last_name']))));
                $spreadsheet->getActiveSheet()->SetCellValue('C' . $i, strtolower(trim($record['email'])));
                $spreadsheet->getActiveSheet()->SetCellValue('D' . $i, $record['brand']);
                $spreadsheet->getActiveSheet()->SetCellValue('E' . $i, $record['manufacturer']);
                $spreadsheet->getActiveSheet()->SetCellValue('F' . $i, $record['model']);
                $spreadsheet->getActiveSheet()->SetCellValue('G' . $i, $record['platform']);
                $spreadsheet->getActiveSheet()->SetCellValue('H' . $i, $record['version']);
                $spreadsheet->getActiveSheet()->SetCellValue('I' . $i, $record['token']);
                $spreadsheet->getActiveSheet()->SetCellValue('J' . $i, $record['updated_on']);
                
                $i++;
            }
            
            
            $fileName = 'reporte_dispositivo_y_acceso_' . time() . '.xls';
            
            $tempFilename = tempnam(sys_get_temp_dir(), 'reporte_dispositivo_y_acceso_' . time());
            
            
            
            
            
            $writer = IOFactory::createWriter($spreadsheet, 'Xls');
            $writer->save($tempFilename);
            
            $content = file_get_contents($tempFilename);
            @unlink($tempFilename);
            
            return new JsonModel([
                'success' => true,
                'data' => [
                    'content' => base64_encode($content),
                    'basename' => $fileName
                    
                ]
            ]);
            
            
            
            
        } else {
            return new JsonModel([
                'success' => false,
                'data' => 'ERROR_METHOD_NOT_ALLOWED'
            ]);
        }
    }
    
    
    public function comentsForCapsulesAction()
    {
        $currentUserPlugin = $this->plugin('currentUserPlugin');
        $currentUser = $currentUserPlugin->getUser();
        $currentCompany = $currentUserPlugin->getCompany();
        
        $request = $this->getRequest();
        
        if($request->isGet())
        {
            
            $headers  = $request->getHeaders();
            
            $isJson = false;
            if($headers->has('Accept')) {
                $accept = $headers->get('Accept');
                
                $prioritized = $accept->getPrioritized();
                
                foreach($prioritized as $key => $value) {
                    $raw = trim($value->getRaw());
                    
                    if(!$isJson) {
                        $isJson = strpos($raw, 'json');
                    }
                    
                }
            }
            
            if($isJson) {
                $topic_uuid     = Functions::sanitizeFilterString($this->params()->fromQuery('topic_uuid'));
                $capsule_uuid   = Functions::sanitizeFilterString($this->params()->fromQuery('capsule_uuid'));
                
                $data = [
                    'items' => [] ,
                    'total' => 0,
                    
                ];
                
                
                if(!$topic_uuid) {
                    return new JsonModel([
                        'success' => true,
                        'data' => $data
                    ]);
                    
                }
                
                
                $topicMapper = MicrolearningTopicMapper::getInstance($this->adapter);
                $topic = $topicMapper->fetchOneByUuid($topic_uuid);
                if(!$topic) {
                    return new JsonModel([
                        'success' => true,
                        'data' => 'ERROR_TOPIC_NOT_FOUND'
                    ]);
                }
                
                if($topic->company_id != $currentCompany->id) {
                    return new JsonModel([
                        'success' => true,
                        'data' => 'ERROR_UNAUTHORIZED'
                    ]);
                }
                
                
                
                if(!$capsule_uuid) {
                    $capsuleMapper = MicrolearningCapsuleMapper::getInstance($this->adapter);
                    $records = $capsuleMapper->fetchAllByCompanyIdAndTopicId($topic->company_id, $topic->id);
                    
                    $capsules = [];
                    foreach($records as $record)
                    {
                        if(!$capsule_uuid) {
                            $capsule_uuid = $record->uuid;
                        }
                        
                        $capsules[ $record->uuid ] = $record->name;
                    }
                    
                    $data['capsules']  = $capsules;
                }
                
                if(!$capsule_uuid) {
                    return new JsonModel([
                        'success' => true,
                        'data' => $data
                    ]);
                    
                }
                
                $capsuleMapper = MicrolearningCapsuleMapper::getInstance($this->adapter);
                $capsule = $capsuleMapper->fetchOneByUuid($capsule_uuid);
                
                
                
                if(!$capsule) {
                    return new JsonModel([
                        'success' => true,
                        'data' => 'ERROR_CAPSULE_NOT_FOUND'
                    ]);
                }
                
                if($capsule->topic_id != $topic->id) {
                    return new JsonModel([
                        'success' => true,
                        'data' => 'ERROR_UNAUTHORIZED'
                    ]);
                }
                

                $search = $this->params()->fromQuery('search', []);
                $search = empty($search['value']) ? '' :  Functions::sanitizeFilterString($search['value']);
                
                $page               = intval($this->params()->fromQuery('start', 1), 10);
                $records_x_page     = intval($this->params()->fromQuery('length', 10), 10);
                $order =  $this->params()->fromQuery('order', []);
                $order_field        = empty($order[0]['column']) ? 99 :  intval($order[0]['column'], 10);
                $order_direction    = empty($order[0]['dir']) ? 'ASC' : strtoupper(Functions::sanitizeFilterString($order[0]['dir']));
                
                $fields =  ['uuid', 'first_name', 'last_name', 'email', 'rating', 'comment', 'added_on'];
                $order_field = isset($fields[$order_field]) ? $fields[$order_field] : 'added_on';
                
                if(!in_array($order_direction, ['ASC', 'DESC'])) {
                    $order_direction = 'DESC';
                }
                

                $queryMapper = QueryMapper::getInstance($this->adapter);
                $sql = $queryMapper->getSql();
                $select = $sql->select();
                $select->columns(['access', 'paid_from', 'paid_to', 'added_on', 'updated_on']);
                $select->from(['tb1' => MicrolearningCapsuleCommentMapper::_TABLE] );
                $select->join(['tb2' => UserMapper::_TABLE], 'tb1.user_id = tb2.id', ['uuid', 'first_name', 'last_name', 'email']);
                $select->where->equalTo('tb1.company_id', $capsule->company_id);
                $select->where->equalTo('tb1.topic_id', $capsule->topic_id);
                $select->where->equalTo('tb1.capsule_id', $capsule->id);
                
                if($search) {
                    $select->where->nest()
                    ->like('first_name', '%' . $search . '%')
                    ->or->like('last_name', '%' . $search . '%')
                    ->or->like('email', '%' . $search . '%')
                    ->unnest();
                    
                }
                
                
                $select->order($order_field . ' ' . $order_direction);
                
                $hydrator   = new ArraySerializableHydrator();
                $resultset  = new HydratingResultSet($hydrator);
                
                $adapter = new DbSelect($select, $sql, $resultset);
                $paginator = new Paginator($adapter);
                $paginator->setItemCountPerPage($records_x_page);
                $paginator->setCurrentPageNumber($page);
                
                
                $items = [ ];
                $records = $paginator->getCurrentItems();
                foreach($records as $record)
                {

                    
                    
                    $dt_added_on = \DateTime::createFromFormat('Y-m-d H:i:s', $record['added_on']);
                    $added_on = $dt_added_on->format('d/m/Y h:i a');
                    

                    $item = [
                        'uuid' => $record['uuid'],
                        'first_name' => ucwords(strtolower($record['first_name'])),
                        'last_name' => ucwords(strtolower($record['last_name'])),
                        'email' => strtolower($record['email']),
                        'rating' => $record['rating'],
                        'text' => $record['text'],
                        'added_on' => $added_on,
                    ];
                    
                    
                    array_push($items, $item);
                    
                    
                }
                
                $data['items'] = $items;
                $data['total'] = $paginator->getTotalItemCount();
                
                
                return new JsonModel([
                    'success' => true,
                    'data' => $data
                ]);
            } else {
                $topicMapper = MicrolearningTopicMapper::getInstance($this->adapter);
                $topics = $topicMapper->fetchAllByCompanyId($currentCompany->id);
                
                if($topics) {
                    $topic_id = $topics[0]->id;
                }  else {
                    $topic_id = 0;
                }
                
                
                $form = new TopicCapsuleForm($this->adapter, $currentCompany->id, $topic_id);
     
                
                
                $this->layout()->setTemplate('layout/layout-backend');
                $viewModel = new ViewModel();
                $viewModel->setTemplate('leaders-linked/microlearning-reports/comments-for-capsule.phtml');
                $viewModel->setVariables([
                    'form' => $form,                    
                ]);
                
                return $viewModel ;
            }
            
        } else {
            return new JsonModel([
                'success' => false,
                'data' => 'ERROR_METHOD_NOT_ALLOWED'
            ]);
        }
    }
    
    
    
    public function progressForCapsuleAction()
    {
        $currentUserPlugin = $this->plugin('currentUserPlugin');
        $currentUser = $currentUserPlugin->getUser();
        $currentCompany = $currentUserPlugin->getCompany();
        
        $request = $this->getRequest();
        
        if($request->isGet())
        {
            
            $headers  = $request->getHeaders();
            
            $isJson = false;
            if($headers->has('Accept')) {
                $accept = $headers->get('Accept');
                
                $prioritized = $accept->getPrioritized();
                
                foreach($prioritized as $key => $value) {
                    $raw = trim($value->getRaw());
                    
                    if(!$isJson) {
                        $isJson = strpos($raw, 'json');
                    }
                    
                }
            }

   
            if($isJson) {
                $acl = $this->getEvent()->getViewModel()->getVariable('acl');
                $allowDownload = $acl->isAllowed($currentUser->usertype_id, 'microlearning/reports/progress-for-capsule/excel');
                
                $topic_uuid     = Functions::sanitizeFilterString($this->params()->fromQuery('topic_uuid'));
                $capsule_uuid   = Functions::sanitizeFilterString($this->params()->fromQuery('capsule_uuid'));

                $topicMapper = MicrolearningTopicMapper::getInstance($this->adapter);
                $topic = $topicMapper->fetchOneByUuid($topic_uuid);
                if(!$topic) {
                    return new JsonModel([
                        'success' => false,
                        'data' => 'ERROR_TOPIC_NOT_FOUND'
                    ]);
                }
                
                if($topic->company_id != $currentCompany->id) {
                    return new JsonModel([
                        'success' => false,
                        'data' => 'ERROR_UNAUTHORIZED'
                    ]);
                }
                
 
                $data = [

                    'items' => [],
                    'link_download' => '',
                    
                ];
                
                if(!$capsule_uuid) {
                    
                    $data['capsules'] = [];
                    
                    $capsuleMapper = MicrolearningCapsuleMapper::getInstance($this->adapter);
                    $records = $capsuleMapper->fetchAllByCompanyIdAndTopicId($topic->company_id, $topic->id);
                    

                    foreach($records as $record)
                    {
                        if(!$capsule_uuid) {
                            $capsule_uuid = $record->uuid;
                        }
                        
                        $data['capsules'][ $record->uuid ] = $record->name;
                    }
                    
                    $data['capsule_selected'] = $capsule_uuid;
                    

                }

         
                
                $capsuleMapper = MicrolearningCapsuleMapper::getInstance($this->adapter);
                $capsule = $capsuleMapper->fetchOneByUuid($capsule_uuid);
                
                
                
                if(!$capsule) {
                    return new JsonModel([
                        'success' => true,
                        'data' => 'ERROR_CAPSULE_NOT_FOUND'
                    ]);
                }
                
                if($capsule->topic_id != $topic->id) {
                    return new JsonModel([
                        'success' => true,
                        'data' => 'ERROR_UNAUTHORIZED'
                    ]);
                }
                

               
                
                
                $queryMapper = QueryMapper::getInstance($this->adapter);
                $sql = $queryMapper->getSql();
                $select = $sql->select();
                $select->columns(['user_id']);
                $select->from(['tb1' => MicrolearningCapsuleUserMapper::_TABLE] );
                $select->join(['tb2' => UserMapper::_TABLE], 'tb1.user_id = tb2.id', ['uuid', 'first_name', 'last_name', 'email']);
                $select->where->equalTo('tb1.company_id', $capsule->company_id);
                $select->where->equalTo('tb1.topic_id', $capsule->topic_id);
                $select->where->equalTo('tb1.capsule_id', $capsule->id);
                $select->where->equalTo('tb2.status', User::STATUS_ACTIVE);
                $select->order('first_name ASC, last_name ASC');
              
                $user_ids = [];
                $records = $queryMapper->fetchAll($select);
              
                foreach($records as $record)
                {
                    $user_id =  $record['user_id'];
                    
                    if(in_array($user_id, $user_ids)) {
                       continue; 
                    }
                    
                    array_push($user_ids, $user_id);
                    
                    $item = [
                        'uuid' => $record['uuid'],
                        'first_name' => ucwords(strtolower($record['first_name'])),
                        'last_name' => ucwords(strtolower($record['last_name'])),
                        'email' => strtolower($record['email']),
                        'progress' => 0, 
                        'total_slides' => 0, 
                        'view_slides' => 0, 
                        'completed' => 0, 
                        'returning_after_completed' => 0, 
                        'added_on' => '', 
                        'updated_on' => ''
                        
                    ]; 
                    
                    $items[ $user_id ] = $item;
                }
                
                
                $select->columns(['user_id', 'progress', 'total_slides', 'view_slides', 'completed', 'returning_after_completed', 'added_on', 'updated_on']);
                $select->from(['tb1' => MicrolearningUserProgressMapper::_TABLE] );
                $select->where->equalTo('tb1.company_id', $capsule->company_id);
                $select->where->equalTo('tb1.type', MicrolearningUserProgress::TYPE_CAPSULE);
                $select->where->equalTo('tb1.topic_id', $capsule->topic_id);
                $select->where->equalTo('tb1.capsule_id', $capsule->id);
                $select->where->in('tb1.user_id', $user_ids);
                

                $records = $queryMapper->fetchAll($select);
                foreach($records as $record)
                {
                    $user_id = $record['user_id'];
                    
                    
                    $dt_added_on = \DateTime::createFromFormat('Y-m-d H:i:s', $record['added_on']);
                    $added_on = $dt_added_on->format('d/m/Y h:i a');
                    
                    $dt_updated_on = \DateTime::createFromFormat('Y-m-d H:i:s', $record['updated_on']);
                    $updated_on = $dt_updated_on->format('d/m/Y h:i a');
                    
                    
                    $items[$user_id]['completed'] = $record['completed'];
                    $items[$user_id]['progress'] = $record['progress'];
                    $items[$user_id]['total_slides'] = $record['total_slides'];
                    $items[$user_id]['view_slides'] = $record['view_slides'];
                    $items[$user_id]['returning_after_completed'] = $record['returning_after_completed'];
                    $items[$user_id]['added_on'] = $added_on;
                    $items[$user_id]['updated_on'] = $updated_on;
                    
                    
                    
                }
                
                $data['items'] = array_values($items);
                
                if($allowDownload) {
                    $data['link_download'] = $this->url()->fromRoute('microlearning/reports/progress-for-capsule/excel', [], ['query' => ['topic_uuid' => $topic->uuid, 'capsule_uuid' => $capsule->uuid] ]);
                }
                
                
                
                return new JsonModel([
                    'success' => true,
                    'data' => $data
                ]);
            } else {
                $topicMapper = MicrolearningTopicMapper::getInstance($this->adapter);
                $topics = $topicMapper->fetchAllByCompanyId($currentCompany->id);
                
                if($topics) {
                    $topic_id = $topics[0]->id;
                }  else {
                    $topic_id = 0;
                }
                
                
                $form = new TopicCapsuleForm($this->adapter, $currentCompany->id, $topic_id);
                
                
                
                $this->layout()->setTemplate('layout/layout-backend');
                $viewModel = new ViewModel();
                $viewModel->setTemplate('leaders-linked/microlearning-reports/progress-for-capsule.phtml');
                $viewModel->setVariables([
                    'form' => $form,
                ]);
                
                return $viewModel ;
            }
            
        } else {
            return new JsonModel([
                'success' => false,
                'data' => 'ERROR_METHOD_NOT_ALLOWED'
            ]);
        }
    }
    
    public function progressForCapsuleExcelAction()
    {
        $currentUserPlugin = $this->plugin('currentUserPlugin');
        $currentUser = $currentUserPlugin->getUser();
        $currentCompany = $currentUserPlugin->getCompany();
        
        $request = $this->getRequest();
        
        if($request->isGet())
        {
            
            $topic_uuid     = Functions::sanitizeFilterString($this->params()->fromQuery('topic_uuid'));
            $capsule_uuid   = Functions::sanitizeFilterString($this->params()->fromQuery('capsule_uuid'));
                
                
            $topicMapper = MicrolearningTopicMapper::getInstance($this->adapter);
            $topic = $topicMapper->fetchOneByUuid($topic_uuid);
            if(!$topic) {
                return new JsonModel([
                    'success' => false,
                    'data' => 'ERROR_TOPIC_NOT_FOUND'
                ]);
            }
                
            if($topic->company_id != $currentCompany->id) {
                return new JsonModel([
                    'success' => false,
                    'data' => 'ERROR_UNAUTHORIZED'
                ]);
            }

            $capsuleMapper = MicrolearningCapsuleMapper::getInstance($this->adapter);
            $capsule = $capsuleMapper->fetchOneByUuid($capsule_uuid);
                
                
                
            if(!$capsule) {
                return new JsonModel([
                    'success' => false,
                    'data' => 'ERROR_CAPSULE_NOT_FOUND'
                ]);
            }
                
            if($capsule->topic_id != $topic->id) {
                return new JsonModel([
                    'success' => false,
                    'data' => 'ERROR_UNAUTHORIZED'
                ]);
            }
                
                
                
                
            $queryMapper = QueryMapper::getInstance($this->adapter);
            $sql = $queryMapper->getSql();
            $select = $sql->select();
            $select->columns(['user_id']);
            $select->from(['tb1' => MicrolearningCapsuleUserMapper::_TABLE] );
            $select->join(['tb2' => UserMapper::_TABLE], 'tb1.user_id = tb2.id', ['uuid', 'first_name', 'last_name', 'email']);
            $select->where->equalTo('tb1.company_id', $capsule->company_id);
            $select->where->equalTo('tb1.topic_id', $capsule->topic_id);
            $select->where->equalTo('tb1.capsule_id', $capsule->id);
            $select->where->equalTo('tb2.status', User::STATUS_ACTIVE);
            $select->order('first_name ASC, last_name ASC');
                
            $user_ids = [];
            $records = $queryMapper->fetchAll($select);
                
            foreach($records as $record)
            {
                $user_id =  $record['user_id'];
                if(in_array($user_id, $user_ids)) {
                    continue;
                }
                    
                array_push($user_ids, $user_id);
                    
                $item = [
                        'uuid' => $record['uuid'],
                        'first_name' => $record['first_name'],
                        'last_name' => $record['last_name'],
                        'email' => $record['email'],
                        'progress' => 0,
                        'total_slides' => 0,
                        'view_slides' => 0,
                        'completed' => 0,
                        'returning_after_completed' => 0,
                        'added_on' => '',
                        'updated_on' => ''
                        
                ];
                    
                $items[ $user_id ] = $item;
            }
                
                
            $select->columns(['user_id', 'progress', 'total_slides', 'view_slides', 'completed', 'returning_after_completed', 'added_on', 'updated_on']);
            $select->from(['tb1' => MicrolearningUserProgressMapper::_TABLE] );
            $select->where->equalTo('tb1.company_id', $capsule->company_id);
            $select->where->equalTo('tb1.type', MicrolearningUserProgress::TYPE_CAPSULE);
            $select->where->equalTo('tb1.topic_id', $capsule->topic_id);
            $select->where->equalTo('tb1.capsule_id', $capsule->id);
            $select->where->in('tb1.user_id', $user_ids);
                
                
            $records = $queryMapper->fetchAll($select);
            foreach($records as $record)
            {
                $user_id = $record['user_id'];
                    
                $dt_added_on = \DateTime::createFromFormat('Y-m-d H:i:s', $record['added_on']);
                $added_on = $dt_added_on->format('d/m/Y h:i a');
                    
                $dt_updated_on = \DateTime::createFromFormat('Y-m-d H:i:s', $record['updated_on']);
                $updated_on = $dt_updated_on->format('d/m/Y h:i a');
                    
                    
                $items[$user_id]['completed'] = $record['completed'];
                $items[$user_id]['progress'] = $record['progress'];
                $items[$user_id]['total_slides'] = $record['total_slides'];
                $items[$user_id]['view_slides'] = $record['view_slides'];
                $items[$user_id]['returning_after_completed'] = $record['returning_after_completed'];
                $items[$user_id]['added_on'] = $added_on;
                $items[$user_id]['updated_on'] = $updated_on;
            }
                
            
            $records = array_values($items);
            
            $spreadsheet = new Spreadsheet();
            $spreadsheet->getProperties()->setTitle("Progreso por Cápsula");
            
            
            
            $spreadsheet->setActiveSheetIndex(0);
            $spreadsheet->getActiveSheet()->SetCellValue('A1', 'Tópico:');
            $spreadsheet->getActiveSheet()->SetCellValue('A2', ucwords(trim(strtolower($topic->name))));
            
            $spreadsheet->getActiveSheet()->SetCellValue('B1', ucwords('Cápsula:'));
            $spreadsheet->getActiveSheet()->SetCellValue('B2', ucwords(trim(strtolower($capsule->name))));
            
            
            $spreadsheet->getActiveSheet()->SetCellValue('C1', 'Fecha:');
            $spreadsheet->getActiveSheet()->SetCellValue('C2', date('d/m/Y h:i a'));
            
            

            $spreadsheet->getActiveSheet()->SetCellValue('A4', 'Nombre');
            $spreadsheet->getActiveSheet()->SetCellValue('B4', 'Apellido');
            $spreadsheet->getActiveSheet()->SetCellValue('C4', 'Correo electrónico');
            $spreadsheet->getActiveSheet()->SetCellValue('D4', 'Progreso');
            $spreadsheet->getActiveSheet()->SetCellValue('E4', 'Diapositivas Totales');
            $spreadsheet->getActiveSheet()->SetCellValue('F4', 'Diapositivas Vistas');
            $spreadsheet->getActiveSheet()->SetCellValue('G4', 'Cápsula Completada');
            $spreadsheet->getActiveSheet()->SetCellValue('H4', 'Inicio');
            $spreadsheet->getActiveSheet()->SetCellValue('I4', 'Ultima vez');

            
            $i = 5;
            foreach($records as $record)
            {
                $spreadsheet->getActiveSheet()->SetCellValue('A' . $i, ucwords(strtolower(trim($record['first_name']))));
                $spreadsheet->getActiveSheet()->SetCellValue('B' . $i, ucwords(strtolower(trim($record['last_name']))));
                $spreadsheet->getActiveSheet()->SetCellValue('C' . $i, strtolower(trim($record['email'])));
                $spreadsheet->getActiveSheet()->SetCellValue('D' . $i, $record['progress']);
                $spreadsheet->getActiveSheet()->SetCellValue('E' . $i, $record['total_slides']);
                $spreadsheet->getActiveSheet()->SetCellValue('F' . $i, $record['view_slides']);
                $spreadsheet->getActiveSheet()->SetCellValue('G' . $i, $record['completed'] == 1 ? 'Si' : 'No');
                $spreadsheet->getActiveSheet()->SetCellValue('H' . $i, $record['added_on']);
                $spreadsheet->getActiveSheet()->SetCellValue('I' . $i, $record['updated_on']);
                $i++;
            }

            
            $fileName = 'reporte_progreso_por_capsula_' . time() . '.xls';
            
            $tempFilename = tempnam(sys_get_temp_dir(), 'reporte_progreso_por_capsula_' . time());
            
            
           
            
            
            $writer = IOFactory::createWriter($spreadsheet, 'Xls');
            $writer->save($tempFilename);
            
            $content = file_get_contents($tempFilename);
            @unlink($tempFilename);
            
            return new JsonModel([
                'success' => true,
                'data' => [
                    'content' => base64_encode($content),
                    'basename' => $fileName
                    
                ]
            ]);
            
            
            
            
        } else {
            return new JsonModel([
                'success' => false,
                'data' => 'ERROR_METHOD_NOT_ALLOWED'
            ]);
        }
    }
   
    
    public function progressForTopicAction()
    {
        $currentUserPlugin = $this->plugin('currentUserPlugin');
        $currentUser = $currentUserPlugin->getUser();
        $currentCompany = $currentUserPlugin->getCompany();
        
        $request = $this->getRequest();
        
        if($request->isGet())
        {
            
            $headers  = $request->getHeaders();
            
            $isJson = false;
            if($headers->has('Accept')) {
                $accept = $headers->get('Accept');
                
                $prioritized = $accept->getPrioritized();
                
                foreach($prioritized as $key => $value) {
                    $raw = trim($value->getRaw());
                    
                    if(!$isJson) {
                        $isJson = strpos($raw, 'json');
                    }
                    
                }
            }
            
            
            if($isJson) {
                $acl = $this->getEvent()->getViewModel()->getVariable('acl');
                $allowDownload = $acl->isAllowed($currentUser->usertype_id, 'microlearning/reports/progress-for-topic/excel');
                
                $topic_uuid     = Functions::sanitizeFilterString($this->params()->fromQuery('topic_uuid'));

                $topicMapper = MicrolearningTopicMapper::getInstance($this->adapter);
                $topic = $topicMapper->fetchOneByUuid($topic_uuid);
                if(!$topic) {
                    return new JsonModel([
                        'success' => false,
                        'data' => 'ERROR_TOPIC_NOT_FOUND'
                    ]);
                }
                
                if($topic->company_id != $currentCompany->id) {
                    return new JsonModel([
                        'success' => false,
                        'data' => 'ERROR_UNAUTHORIZED'
                    ]);
                }
                
                
                $data = [
                    
                    'items' => [],
                    'link_download' => '',
                    
                ];
                
                
                
                $queryMapper = QueryMapper::getInstance($this->adapter);
                $sql = $queryMapper->getSql();
                $select = $sql->select();
                $select->columns(['user_id' => new Expression('DISTINCT(user_id)') ]);
                $select->from(['tb1' => MicrolearningCapsuleUserMapper::_TABLE] );
                $select->join(['tb2' => UserMapper::_TABLE], 'tb1.user_id = tb2.id', ['uuid', 'first_name', 'last_name', 'email']);
                $select->where->equalTo('tb1.company_id', $topic->company_id);
                $select->where->equalTo('tb1.topic_id', $topic->id);
                $select->where->equalTo('tb2.status', User::STATUS_ACTIVE);
                $select->order('first_name ASC, last_name ASC');
                
                $user_ids = [];
                $records = $queryMapper->fetchAll($select);
                
                foreach($records as $record)
                {
                    $user_id =  $record['user_id'];
                    
                    if(in_array($user_id, $user_ids)) {
                        continue;
                    }
                    
                    array_push($user_ids, $user_id);
                    
                    $item = [
                        'uuid' => $record['uuid'],
                        'first_name' => ucwords(strtolower($record['first_name'])),
                        'last_name' => ucwords(strtolower($record['last_name'])),
                        'email' => strtolower($record['email']),
                        'progress' => 0,
                        'total_capsules' => 0,
                        'completed_capsules' => 0,
                        'started_capsules' => 0,
                        'pending_capsules' => 0,
                        'added_on' => '',
                        'updated_on' => ''
                        
                    ];
                    
                    $items[ $user_id ] = $item;
                }
                
                $select = $sql->select();
                $select->columns(['total' => new Expression('COUNT(*)'), 'user_id']);
                $select->from(MicrolearningCapsuleUserMapper::_TABLE);
                $select->where->equalTo('company_id', $topic->company_id);
                $select->where->equalTo('topic_id', $topic->id);
                $select->where->in('user_id', $user_ids);
                $select->group('user_id');
                
                //echo $select->getSqlString($this->adapter->platform); 
                
                $records = $queryMapper->fetchAll($select);
                foreach($records as $record)
                {
                    $user_id = $record['user_id'];
                    
                    $items[$user_id]['total_capsules'] = $record['total'];
                
                }
                
                
                $select = $sql->select();
                $select->columns(['total' => new Expression('COUNT(*)'), 'user_id']);
                $select->from(MicrolearningUserProgressMapper::_TABLE);
                $select->where->equalTo('company_id', $topic->company_id);
                $select->where->equalTo('topic_id', $topic->id);
                $select->where->equalTo('type', MicrolearningUserProgress::TYPE_CAPSULE);
                $select->where->greaterThanOrEqualTo('progress', 100);
                $select->where->equalTo('completed', 1);
                $select->where->in('user_id', $user_ids);
                $select->group('user_id');

                
                //echo $select->getSqlString($this->adapter->platform); 

                
                $records = $queryMapper->fetchAll($select);
                foreach($records as $record)
                {
                    $user_id = $record['user_id'];
                    
                    $items[$user_id]['completed_capsules'] = $record['total'];
                    
                }
                
                
                $select = $sql->select();
                $select->columns(['total' => new Expression('COUNT(*)'), 'user_id']);
                $select->from(MicrolearningUserProgressMapper::_TABLE);
                $select->where->equalTo('company_id', $topic->company_id);
                $select->where->equalTo('topic_id', $topic->id);
                $select->where->equalTo('type', MicrolearningUserProgress::TYPE_CAPSULE);
                $select->where->lessThan('progress', 100);
                $select->where->equalTo('completed', 0);
                $select->where->in('user_id', $user_ids);
                $select->group('user_id');

                //echo $select->getSqlString($this->adapter->platform); 
                
                
                $records = $queryMapper->fetchAll($select);
                foreach($records as $record)
                {
                    $user_id = $record['user_id'];
                    
                    $items[$user_id]['started_capsules'] = $record['total'];
                    
                }
                
                $select = $sql->select();
                $select->columns(['added_on' => new Expression('MIN(added_on)'), 'updated_on' => new Expression('MAX(updated_on)'), 'user_id']);
                $select->from(MicrolearningUserProgressMapper::_TABLE);
                $select->where->equalTo('company_id', $topic->company_id);
                $select->where->equalTo('topic_id', $topic->id);
                $select->where->equalTo('type', MicrolearningUserProgress::TYPE_CAPSULE);
                $select->where->in('user_id', $user_ids);
                $select->group('user_id');
                
                
                $records = $queryMapper->fetchAll($select);

                
                
                foreach($records as $record)
                {
                    $user_id = $record['user_id'];

                    
                    $dt_added_on = \DateTime::createFromFormat('Y-m-d H:i:s', $record['added_on']);
                    $added_on = $dt_added_on->format('d/m/Y h:i a');
                    
                    $dt_updated_on = \DateTime::createFromFormat('Y-m-d H:i:s', $record['updated_on']);
                    $updated_on = $dt_updated_on->format('d/m/Y h:i a');
                    
                    
                    $items[$user_id]['added_on'] = $added_on;
                    $items[$user_id]['updated_on'] = $updated_on;

                    if($items[$user_id]['completed_capsules']) {
                        
                        $progress = ($items[$user_id]['completed_capsules'] * 100) / $items[$user_id]['total_capsules'];
                        
                        $items[$user_id]['progress'] = number_format($progress, 2);
                    }
                }
                
                $data['items'] = array_values($items);
                
                if($allowDownload) {
                    $data['link_download'] = $this->url()->fromRoute('microlearning/reports/progress-for-topic/excel', [], ['query' => ['topic_uuid' => $topic->uuid] ]);
                }
                
                
                
                return new JsonModel([
                    'success' => true,
                    'data' => $data
                ]);
            } else {
                
                
                $form = new TopicReportForm($this->adapter, $currentCompany->id);
                
                
                
                $this->layout()->setTemplate('layout/layout-backend');
                $viewModel = new ViewModel();
                $viewModel->setTemplate('leaders-linked/microlearning-reports/progress-for-topic.phtml');
                $viewModel->setVariables([
                    'form' => $form,
                ]);
                
                return $viewModel ;
            }
            
        } else {
            return new JsonModel([
                'success' => false,
                'data' => 'ERROR_METHOD_NOT_ALLOWED'
            ]);
        }
    }
    
    public function progressForTopicExcelAction()
    {
        $currentUserPlugin = $this->plugin('currentUserPlugin');
        $currentUser = $currentUserPlugin->getUser();
        $currentCompany = $currentUserPlugin->getCompany();
        
        $request = $this->getRequest();
        
        if($request->isGet())
        {
            
            $topic_uuid     = Functions::sanitizeFilterString($this->params()->fromQuery('topic_uuid'));
            
            $topicMapper = MicrolearningTopicMapper::getInstance($this->adapter);
            $topic = $topicMapper->fetchOneByUuid($topic_uuid);
            if(!$topic) {
                return new JsonModel([
                    'success' => false,
                    'data' => 'ERROR_TOPIC_NOT_FOUND'
                ]);
            }
            
            if($topic->company_id != $currentCompany->id) {
                return new JsonModel([
                    'success' => false,
                    'data' => 'ERROR_UNAUTHORIZED'
                ]);
            }
            
            $queryMapper = QueryMapper::getInstance($this->adapter);
            $sql = $queryMapper->getSql();
            $select = $sql->select();
            $select->columns(['user_id' => new Expression('DISTINCT(user_id)') ]);
            $select->from(['tb1' => MicrolearningCapsuleUserMapper::_TABLE] );
            $select->join(['tb2' => UserMapper::_TABLE], 'tb1.user_id = tb2.id', ['uuid', 'first_name', 'last_name', 'email']);
            $select->where->equalTo('tb1.company_id', $topic->company_id);
            $select->where->equalTo('tb1.topic_id', $topic->id);
            $select->where->equalTo('tb2.status', User::STATUS_ACTIVE);
            $select->order('first_name ASC, last_name ASC');
            
            $user_ids = [];
            $records = $queryMapper->fetchAll($select);
            
            foreach($records as $record)
            {
                $user_id =  $record['user_id'];
                
                if(in_array($user_id, $user_ids)) {
                    continue;
                }
                
                array_push($user_ids, $user_id);
                
                $item = [
                    'uuid' => $record['uuid'],
                    'first_name' => $record['first_name'],
                    'last_name' => $record['last_name'],
                    'email' => $record['email'],
                    'progress' => 0,
                    'total_capsules' => 0,
                    'completed_capsules' => 0,
                    'started_capsules' => 0,
                    'pending_capsules' => 0,
                    'added_on' => '',
                    'updated_on' => ''
                    
                ];
                
                $items[ $user_id ] = $item;
            }
            
            $select = $sql->select();
            $select->columns(['total' => new Expression('COUNT(*)'), 'user_id']);
            $select->from(MicrolearningCapsuleUserMapper::_TABLE);
            $select->where->equalTo('company_id', $topic->company_id);
            $select->where->equalTo('topic_id', $topic->id);
            $select->group('user_id');
            
            $records = $queryMapper->fetchAll($select);
            foreach($records as $record)
            {
                $user_id = $record['user_id'];
                
                $items[$user_id]['total_capsules'] = $record['total'];
                
            }
            
            
            $select = $sql->select();
            $select->columns(['total' => new Expression('COUNT(*)'), 'user_id']);
            $select->from(MicrolearningUserProgressMapper::_TABLE);
            $select->where->equalTo('company_id', $topic->company_id);
            $select->where->equalTo('topic_id', $topic->id);
            $select->where->equalTo('type', MicrolearningUserProgress::TYPE_CAPSULE);
            $select->where->greaterThanOrEqualTo('progress', 100);
            $select->where->equalTo('completed', 1);
            $select->group('user_id');
            
            //echo $select->getSqlString($this->adapter->platform); exit;
            
            
            $records = $queryMapper->fetchAll($select);
            foreach($records as $record)
            {
                $user_id = $record['user_id'];
                
                $items[$user_id]['completed_capsules'] = $record['total'];
                
            }
            
            
            $select = $sql->select();
            $select->columns(['total' => new Expression('COUNT(*)'), 'user_id']);
            $select->from(MicrolearningUserProgressMapper::_TABLE);
            $select->where->equalTo('company_id', $topic->company_id);
            $select->where->equalTo('topic_id', $topic->id);
            $select->where->equalTo('type', MicrolearningUserProgress::TYPE_CAPSULE);
            $select->where->lessThan('progress', 100);
            $select->where->equalTo('completed', 0);
            $select->group('user_id');
            
            
            $records = $queryMapper->fetchAll($select);
            foreach($records as $record)
            {
                $user_id = $record['user_id'];
                
                $items[$user_id]['started_capsules'] = $record['total'];
                
            }
            
            $select = $sql->select();
            $select->columns(['added_on' => new Expression('MIN(added_on)'), 'updated_on' => new Expression('MAX(updated_on)'), 'user_id']);
            $select->from(MicrolearningUserProgressMapper::_TABLE);
            $select->where->equalTo('company_id', $topic->company_id);
            $select->where->equalTo('topic_id', $topic->id);
            $select->where->equalTo('type', MicrolearningUserProgress::TYPE_CAPSULE);
            $select->group('user_id');
            
            
            $records = $queryMapper->fetchAll($select);
            foreach($records as $record)
            {
                $user_id = $record['user_id'];
                
                
                $dt_added_on = \DateTime::createFromFormat('Y-m-d H:i:s', $record['added_on']);
                $added_on = $dt_added_on->format('d/m/Y h:i a');
                
                $dt_updated_on = \DateTime::createFromFormat('Y-m-d H:i:s', $record['updated_on']);
                $updated_on = $dt_updated_on->format('d/m/Y h:i a');
                
                
                $items[$user_id]['added_on'] = $added_on;
                $items[$user_id]['updated_on'] = $updated_on;
                
                if($items[$user_id]['completed_capsules']) {
                    
                    $progress = ($items[$user_id]['completed_capsules'] * 100) / $items[$user_id]['total_capsules'];
                    
                    $items[$user_id]['progress'] = number_format($progress, 2);
                }
                $items[$user_id]['pending_capsules'] = $items[$user_id]['total_capsules'] - ($items[$user_id]['completed_capsules' ] + $items[$user_id]['started_capsules']);

            }

            $records = array_values($items);
            
            $spreadsheet = new Spreadsheet();
            $spreadsheet->getProperties()->setTitle("Progreso por Tópico");
            
            
            
            $spreadsheet->setActiveSheetIndex(0);
            $spreadsheet->getActiveSheet()->SetCellValue('A1', 'Tópico:');
            $spreadsheet->getActiveSheet()->SetCellValue('A2', ucwords(trim(strtolower($topic->name))));
            

            
            $spreadsheet->getActiveSheet()->SetCellValue('B1', 'Fecha:');
            $spreadsheet->getActiveSheet()->SetCellValue('N2', date('d/m/Y h:i a'));
            
            
            
            $spreadsheet->getActiveSheet()->SetCellValue('A4', 'Nombre');
            $spreadsheet->getActiveSheet()->SetCellValue('B4', 'Apellido');
            $spreadsheet->getActiveSheet()->SetCellValue('C4', 'Correo electrónico');
            $spreadsheet->getActiveSheet()->SetCellValue('D4', 'Progreso');
            $spreadsheet->getActiveSheet()->SetCellValue('E4', 'Capsulas Totales');
            $spreadsheet->getActiveSheet()->SetCellValue('F4', 'Capsulas Completadas');
            $spreadsheet->getActiveSheet()->SetCellValue('G4', 'Capsulas en Progreso');
            $spreadsheet->getActiveSheet()->SetCellValue('H4', 'Capsulas sin Iniciar');
            $spreadsheet->getActiveSheet()->SetCellValue('I4', 'Inicio');
            $spreadsheet->getActiveSheet()->SetCellValue('J4', 'Ultima vez');
            
            $i = 5;
            foreach($records as $record)
            {
                $spreadsheet->getActiveSheet()->SetCellValue('A' . $i, ucwords(strtolower(trim($record['first_name']))));
                $spreadsheet->getActiveSheet()->SetCellValue('B' . $i, ucwords(strtolower(trim($record['last_name']))));
                $spreadsheet->getActiveSheet()->SetCellValue('C' . $i, strtolower(trim($record['email'])));
                $spreadsheet->getActiveSheet()->SetCellValue('D' . $i, $record['progress']);
                $spreadsheet->getActiveSheet()->SetCellValue('E' . $i, $record['total_capsules']);
                $spreadsheet->getActiveSheet()->SetCellValue('F' . $i, $record['completed_capsules']);
                $spreadsheet->getActiveSheet()->SetCellValue('G' . $i, $record['started_capsules']);
                $spreadsheet->getActiveSheet()->SetCellValue('H' . $i, $record['pending_capsules']);
                $spreadsheet->getActiveSheet()->SetCellValue('I' . $i, $record['added_on']);
                $spreadsheet->getActiveSheet()->SetCellValue('J' . $i, $record['updated_on']);
                $i++;
            }
            
            
            $fileName = 'reporte_progreso_por_topico_' . time() . '.xls';
            
            $tempFilename = tempnam(sys_get_temp_dir(), 'reporte_progreso_por_topico_' . time());
            
            
            
            
            
            $writer = IOFactory::createWriter($spreadsheet, 'Xls');
            $writer->save($tempFilename);
            
            $content = file_get_contents($tempFilename);
            @unlink($tempFilename);
            
            return new JsonModel([
                'success' => true,
                'data' => [
                    'content' => base64_encode($content),
                    'basename' => $fileName
                    
                ]
            ]);
            
            
            
            
        } else {
            return new JsonModel([
                'success' => false,
                'data' => 'ERROR_METHOD_NOT_ALLOWED'
            ]);
        }
    }
    
    
    public function progressForStudentAction()
    {
        $currentUserPlugin = $this->plugin('currentUserPlugin');
        $currentUser = $currentUserPlugin->getUser();
        $currentCompany = $currentUserPlugin->getCompany();
        
        $request = $this->getRequest();
        
        if($request->isGet())
        {
            
            $headers  = $request->getHeaders();
            
            $isJson = false;
            if($headers->has('Accept')) {
                $accept = $headers->get('Accept');
                
                $prioritized = $accept->getPrioritized();
                
                foreach($prioritized as $key => $value) {
                    $raw = trim($value->getRaw());
                    
                    if(!$isJson) {
                        $isJson = strpos($raw, 'json');
                    }
                    
                }
            }
            
            
            if($isJson) {
                
                $acl = $this->getEvent()->getViewModel()->getVariable('acl');
                $allowDownload = $acl->isAllowed($currentUser->usertype_id, 'microlearning/reports/progress-for-student/excel');

                $student_uuid     = Functions::sanitizeFilterString($this->params()->fromQuery('student_uuid'));
                
                $userMapper = UserMapper::getInstance($this->adapter);
                $user = $userMapper->fetchOneByUuidAndNetworkId($student_uuid, $currentUser->network_id);
                
                
                if(!$user) {
                    return new JsonModel([
                        'success' => false,
                        'data' => 'ERROR_USER_NOT_FOUND'
                    ]);
                }
                
                
                $microlearningCapsuleUserMapper = MicrolearningCapsuleUserMapper::getInstance($this->adapter);
                $count = $microlearningCapsuleUserMapper->fetchCountByCompanyIdAndUserId($currentCompany->id, $user->id);
                

                if(!$count) {
                    return new JsonModel([
                        'success' => false,
                        'data' => 'ERROR_UNAUTHORIZED'
                    ]);
                }
                
                
                $data = [
                    'items' => [],
                ];
                
                
                $queryMapper = QueryMapper::getInstance($this->adapter);
                $sql = $queryMapper->getSql();
                $select = $sql->select();
                $select->columns(['capsule_id', 'access', 'paid_from', 'paid_to' ]);
                $select->from(['tb1' => MicrolearningCapsuleUserMapper::_TABLE] );
                $select->join(['tb2' => MicrolearningTopicMapper::_TABLE], 'tb1.company_id = tb2.company_id AND tb1.topic_id = tb2.id', ['topic' => 'name']);
                $select->join(['tb3' => MicrolearningCapsuleMapper::_TABLE], 'tb1.company_id = tb3.company_id AND tb1.topic_id = tb3.topic_id AND tb1.capsule_id = tb3.id', ['capsule' => 'name']);
                $select->where->equalTo('tb1.company_id', $currentCompany->id);
                $select->where->equalTo('tb1.user_id', $user->id);
                $select->order('tb2.name ASC, tb3.name ASC');
 

                $items = [];
                $records = $queryMapper->fetchAll($select);
                foreach($records as $record)
                {
                    $capsule_id = $record['capsule_id'];

                    
                    $item = [
                        'topic' => ucwords(strtolower($record['topic'])),
                        'capsule' => ucwords(strtolower($record['capsule'])),
                        'progress' => 0,
                        'total_slides' => 0,
                        'view_slides' => 0,
                        'completed' => 'No',
                        'added_on' => '',
                        'updated_on' => '',
                        
                    ];
                    
                    $items[ $record['capsule_id'] ] =  $item;
                }

                
                
                $queryMapper = QueryMapper::getInstance($this->adapter);
                $sql = $queryMapper->getSql();
                $select = $sql->select(MicrolearningUserProgressMapper::_TABLE);
                $select->columns(['capsule_id', 'progress', 'total_slides' , 'view_slides', 'completed', 'added_on','updated_on']);
                $select->where->equalTo('company_id', $currentCompany->id);
                $select->where->equalTo('user_id', $user->id);
                $select->where->equalTo('type', MicrolearningUserProgress::TYPE_CAPSULE);

                
                
                
                $records = $queryMapper->fetchAll($select);
                
                
         
                foreach($records as $record)
                {
                    $capsule_id = $record['capsule_id'];
                    $dt_added_on = \DateTime::createFromFormat('Y-m-d H:i:s', $record['added_on']);
                    $added_on = $dt_added_on->format('d/m/Y h:i a');
                    
                    $dt_updated_on = \DateTime::createFromFormat('Y-m-d H:i:s', $record['updated_on']);
                    $updated_on = $dt_updated_on->format('d/m/Y h:i a');
                    
                    
                    $items[ $capsule_id ]['progress'] = $record['progress'];
                    $items[ $capsule_id ]['total_slides'] = $record['total_slides'];
                    $items[ $capsule_id ]['view_slides'] = $record['view_slides'];
                    $items[ $capsule_id ]['completed'] = $record['completed'] == 1 ? 'Si' : 'No';
                    $items[ $capsule_id ]['added_on'] = $added_on;
                    $items[ $capsule_id ]['updated_on'] = $updated_on;
                }
                
                
                $data['items'] = array_values($items);
                
                if($allowDownload) {
                    $data['link_download'] = $this->url()->fromRoute('microlearning/reports/progress-for-student/excel', [], ['query' => ['student_uuid' => $user->uuid] ]);
                }
                
                
                
                return new JsonModel([
                    'success' => true,
                    'data' => $data
                ]);
            } else {
                
                
                $form = new StudentReportForm($this->adapter, $currentCompany->id);
                
                
                
                $this->layout()->setTemplate('layout/layout-backend');
                $viewModel = new ViewModel();
                $viewModel->setTemplate('leaders-linked/microlearning-reports/progress-for-student.phtml');
                $viewModel->setVariables([
                    'form' => $form,
                ]);
                
                return $viewModel ;
            }
            
        } else {
            return new JsonModel([
                'success' => false,
                'data' => 'ERROR_METHOD_NOT_ALLOWED'
            ]);
        }
    }
    
    public function progressForStudentExcelAction()
    {
        $currentUserPlugin = $this->plugin('currentUserPlugin');
        $currentUser = $currentUserPlugin->getUser();
        $currentCompany = $currentUserPlugin->getCompany();
        
        $request = $this->getRequest();
        
        if($request->isGet())
        {
            
            $student_uuid     = Functions::sanitizeFilterString($this->params()->fromQuery('student_uuid'));
            
            $userMapper = UserMapper::getInstance($this->adapter);
            $user = $userMapper->fetchOneByUuidAndNetworkId($student_uuid, $currentUser->network_id);
            
            
            if(!$user) {
                return new JsonModel([
                    'success' => false,
                    'data' => 'ERROR_USER_NOT_FOUND'
                ]);
            }
            
            $microlearningCapsuleUserMapper = MicrolearningCapsuleUserMapper::getInstance($this->adapter);
            $count = $microlearningCapsuleUserMapper->fetchCountByCompanyIdAndUserId($currentCompany->id, $user->id);
            
            
            if(!$count) {
                return new JsonModel([
                    'success' => false,
                    'data' => 'ERROR_UNAUTHORIZED'
                ]);
            }
            

            
            
            $queryMapper = QueryMapper::getInstance($this->adapter);
            $sql = $queryMapper->getSql();
            $select = $sql->select();
            $select->columns(['capsule_id', 'access', 'paid_from', 'paid_to' ]);
            $select->from(['tb1' => MicrolearningCapsuleUserMapper::_TABLE] );
            $select->join(['tb2' => MicrolearningTopicMapper::_TABLE], 'tb1.company_id = tb2.company_id AND tb1.topic_id = tb2.id', ['topic' => 'name']);
            $select->join(['tb3' => MicrolearningCapsuleMapper::_TABLE], 'tb1.company_id = tb3.company_id AND tb1.topic_id = tb3.topic_id AND tb1.capsule_id = tb3.id', ['capsule' => 'name']);
            $select->where->equalTo('tb1.company_id', $currentCompany->id);
            $select->where->equalTo('tb1.user_id', $user->id);
            $select->order('tb2.name ASC, tb3.name ASC');
            
            
            $items = [];
            $records = $queryMapper->fetchAll($select);
            foreach($records as $record)
            {
                $capsule_id = $record['capsule_id'];
                
                
                $item = [
                    'topic' => ucwords(strtolower($record['topic'])),
                    'capsule' => ucwords(strtolower($record['capsule'])),
                    'progress' => 0,
                    'total_slides' => 0,
                    'view_slides' => 0,
                    'completed' => 'No',
                    'added_on' => '',
                    'updated_on' => '',
                    
                ];
                
                $items[ $record['capsule_id'] ] =  $item;
            }
            
            
            
            $queryMapper = QueryMapper::getInstance($this->adapter);
            $sql = $queryMapper->getSql();
            $select = $sql->select(MicrolearningUserProgressMapper::_TABLE);
            $select->columns(['capsule_id', 'progress', 'total_slides' , 'view_slides', 'completed', 'added_on','updated_on']);
            $select->where->equalTo('company_id', $currentCompany->id);
            $select->where->equalTo('user_id', $user->id);
            $select->where->equalTo('type', MicrolearningUserProgress::TYPE_CAPSULE);
            
            
            
            
            $records = $queryMapper->fetchAll($select);
            
            
            
            foreach($records as $record)
            {
                $capsule_id = $record['capsule_id'];
                $dt_added_on = \DateTime::createFromFormat('Y-m-d H:i:s', $record['added_on']);
                $added_on = $dt_added_on->format('d/m/Y h:i a');
                
                $dt_updated_on = \DateTime::createFromFormat('Y-m-d H:i:s', $record['updated_on']);
                $updated_on = $dt_updated_on->format('d/m/Y h:i a');
                
                
                $items[ $capsule_id ]['progress'] = $record['progress'];
                $items[ $capsule_id ]['total_slides'] = $record['total_slides'];
                $items[ $capsule_id ]['view_slides'] = $record['view_slides'];
                $items[ $capsule_id ]['completed'] = $record['completed'];
                $items[ $capsule_id ]['added_on'] = $added_on;
                $items[ $capsule_id ]['updated_on'] = $updated_on;
            }
            
            

            
            $records = array_values($items);
            
            
            $spreadsheet = new Spreadsheet();
            $spreadsheet->getProperties()->setTitle("Progreso por Tópico");
            
            
            
            $spreadsheet->setActiveSheetIndex(0);
            $spreadsheet->getActiveSheet()->SetCellValue('A1', 'Nombre:');
            $spreadsheet->getActiveSheet()->SetCellValue('A2', ucwords(trim(strtolower($user->first_name))));
            $spreadsheet->getActiveSheet()->SetCellValue('B1', 'Apellido:');
            $spreadsheet->getActiveSheet()->SetCellValue('B2', ucwords(trim(strtolower($user->last_name))));
            $spreadsheet->getActiveSheet()->SetCellValue('C1', 'Correo electrónco:');
            $spreadsheet->getActiveSheet()->SetCellValue('C2', trim(strtolower($user->email)));
            $spreadsheet->getActiveSheet()->SetCellValue('D1', 'Fecha:');
            $spreadsheet->getActiveSheet()->SetCellValue('D2', date('d/m/Y h:i a'));
            
            
            
            $spreadsheet->getActiveSheet()->SetCellValue('A4', 'Tópico');
            $spreadsheet->getActiveSheet()->SetCellValue('B4', 'Cápsula');
            $spreadsheet->getActiveSheet()->SetCellValue('C4', 'Progreso');
            $spreadsheet->getActiveSheet()->SetCellValue('D4', 'Diapositivas Totales');
            $spreadsheet->getActiveSheet()->SetCellValue('E4', 'Diapositivas Vistas');
            $spreadsheet->getActiveSheet()->SetCellValue('F4', 'Cápsula Completada');
            $spreadsheet->getActiveSheet()->SetCellValue('G4', 'Inicio');
            $spreadsheet->getActiveSheet()->SetCellValue('H4', 'Ultima vez');
            
            $i = 5;
            foreach($records as $record)
            {
                $spreadsheet->getActiveSheet()->SetCellValue('A' . $i, ucwords(strtolower(trim($record['topic']))));
                $spreadsheet->getActiveSheet()->SetCellValue('B' . $i, ucwords(strtolower(trim($record['capsule']))));
                $spreadsheet->getActiveSheet()->SetCellValue('C' . $i, $record['progress']);
                $spreadsheet->getActiveSheet()->SetCellValue('D' . $i, $record['total_slides']);
                $spreadsheet->getActiveSheet()->SetCellValue('E' . $i, $record['view_slides']);
                $spreadsheet->getActiveSheet()->SetCellValue('F' . $i, $record['completed'] == 1 ? 'Si' : 'No');
                $spreadsheet->getActiveSheet()->SetCellValue('G' . $i, $record['added_on']);
                $spreadsheet->getActiveSheet()->SetCellValue('H' . $i, $record['updated_on']);
                $i++;
            }

            
            $fileName = 'reporte_progreso_por_estudiante_' . time() . '.xls';
            
            $tempFilename = tempnam(sys_get_temp_dir(), 'reporte_progreso_por_estudiante_' . time());
            
            
            
            
            
            $writer = IOFactory::createWriter($spreadsheet, 'Xls');
            $writer->save($tempFilename);
            
            $content = file_get_contents($tempFilename);
            @unlink($tempFilename);
            
            return new JsonModel([
                'success' => true,
                'data' => [
                    'content' => base64_encode($content),
                    'basename' => $fileName
                    
                ]
            ]);
            
            
            
            
        } else {
            return new JsonModel([
                'success' => false,
                'data' => 'ERROR_METHOD_NOT_ALLOWED'
            ]);
        }
    }
    
    
}