Proyectos de Subversion LeadersLinked - Backend

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;

                }

            if($isJson) {

                        'success' => false,

                        'data' => 'ERROR_UNAUTHORIZED'

                    ]);

                }

                    'items' => [],

                    'link_download' => '',

                    foreach($records as $record)

                    {

                $select->join(['tb2' => UserMapper::_TABLE], 'tb1.user_id = tb2.id', ['uuid', 'first_name', 'last_name', 'email']);

                $select->where->equalTo('tb2.status', User::STATUS_ACTIVE);

                $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' => CompanyMicrolearningUserProgressMapper::_TABLE] );

                $select->where->equalTo('tb1.company_id', $capsule->company_id);

                $select->where->equalTo('tb1.type', CompanyMicrolearningUserProgress::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);

                    $items[$user_id]['completed'] = $record['completed'];

                    $items[$user_id]['progress'] = $record['progress'];

                    $items[$user_id]['total_slides'] = $record['total_slides'];

    public function progressForCapsuleExcelAction()

    {

        $currentUserPlugin = $this->plugin('currentUserPlugin');

        $currentUser = $currentUserPlugin->getUser();

        $currentCompany = $currentUserPlugin->getCompany();

        $request = $this->getRequest();

        if($request->isGet())

        {

            $topic_uuid     = filter_var($this->params()->fromQuery('topic_uuid'), FILTER_SANITIZE_STRING);

            $capsule_uuid   = filter_var($this->params()->fromQuery('capsule_uuid'), FILTER_SANITIZE_STRING);

            $topicMapper = CompanyMicrolearningTopicMapper::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 = CompanyMicrolearningCapsuleMapper::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' => CompanyMicrolearningCapsuleUserMapper::_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' => CompanyMicrolearningUserProgressMapper::_TABLE] );

            $select->where->equalTo('tb1.company_id', $capsule->company_id);

            $select->where->equalTo('tb1.type', CompanyMicrolearningUserProgress::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     = filter_var($this->params()->fromQuery('topic_uuid'), FILTER_SANITIZE_STRING);

                $topicMapper = CompanyMicrolearningTopicMapper::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' => CompanyMicrolearningCapsuleUserMapper::_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(CompanyMicrolearningCapsuleUserMapper::_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(CompanyMicrolearningUserProgressMapper::_TABLE);

                $select->where->equalTo('company_id', $topic->company_id);

                $select->where->equalTo('topic_id', $topic->id);

                $select->where->equalTo('type', CompanyMicrolearningUserProgress::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(CompanyMicrolearningUserProgressMapper::_TABLE);

                $select->where->equalTo('company_id', $topic->company_id);

                $select->where->equalTo('topic_id', $topic->id);

                $select->where->equalTo('type', CompanyMicrolearningUserProgress::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(CompanyMicrolearningUserProgressMapper::_TABLE);

                $select->where->equalTo('company_id', $topic->company_id);

                $select->where->equalTo('topic_id', $topic->id);

                $select->where->equalTo('type', CompanyMicrolearningUserProgress::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);

                    }

                }

                $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     = filter_var($this->params()->fromQuery('topic_uuid'), FILTER_SANITIZE_STRING);

            $topicMapper = CompanyMicrolearningTopicMapper::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' => CompanyMicrolearningCapsuleUserMapper::_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(CompanyMicrolearningCapsuleUserMapper::_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(CompanyMicrolearningUserProgressMapper::_TABLE);

            $select->where->equalTo('company_id', $topic->company_id);

            $select->where->equalTo('topic_id', $topic->id);

            $select->where->equalTo('type', CompanyMicrolearningUserProgress::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(CompanyMicrolearningUserProgressMapper::_TABLE);

            $select->where->equalTo('company_id', $topic->company_id);

            $select->where->equalTo('topic_id', $topic->id);

            $select->where->equalTo('type', CompanyMicrolearningUserProgress::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(CompanyMicrolearningUserProgressMapper::_TABLE);

            $select->where->equalTo('company_id', $topic->company_id);

            $select->where->equalTo('topic_id', $topic->id);

            $select->where->equalTo('type', CompanyMicrolearningUserProgress::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);

                }

            }

            $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     = filter_var($this->params()->fromQuery('student_uuid'), FILTER_SANITIZE_STRING);

                $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'

                    ]);

                }

                $companyMicrolearningCapsuleUserMapper = CompanyMicrolearningCapsuleUserMapper::getInstance($this->adapter);

                $count = $companyMicrolearningCapsuleUserMapper->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(['access', 'paid_from', 'paid_to' ]);

                $select->from(['tb1' => CompanyMicrolearningCapsuleUserMapper::_TABLE] );

                $select->join(['tb2' => CompanyMicrolearningTopicMapper::_TABLE], 'tb1.company_id = tb2.company_id AND tb1.topic_id = tb2.id', ['topic' => 'name']);

                $select->join(['tb3' => CompanyMicrolearningCapsuleMapper::_TABLE], 'tb1.company_id = tb3.company_id AND tb1.topic_id = tb3.topic_id AND tb1.capsule_id = tb3.id', ['capsule' => 'name']);

                $select->join(['tb4' => CompanyMicrolearningUserProgressMapper::_TABLE], 

                    'tb1.company_id = tb2.company_id AND tb1.topic_id = tb4.topic_id AND tb1.capsule_id = tb4.capsule_id AND tb1.user_id = tb4.user_id', 

                    ['progress', 'total_slides' , 'view_slides', 'completed', 'added_on','updated_on'], Select::JOIN_RIGHT_OUTER);

                $select->where->equalTo('tb1.company_id', $currentCompany->id);

                $select->where->equalTo('tb1.user_id', $user->id);

                $select->where->equalTo('tb4.type', CompanyMicrolearningUserProgress::TYPE_CAPSULE);