<?php
ini_set('session.gc_maxlifetime', 10800);
session_set_cookie_params(10800);
session_name('delphi');
session_start();
require('classes/mysqliConn.php');
require('classes/functions.php');
class main extends mysqliConn {
    public $mysqli;
    private $token;
    private $stringlogin;
    private $maxLoginAttemps = 100;
    public $title = 'DELPHI';
    public $checkToken = true;
    public $loginCheck = false;
    public $adminCheck = false;
    public $err = '';
    public $suc = '';
    public $documentsPath = 'documents/';
    function __construct() {
        $this->mysqli = parent::dbconn();
        $_GET = $this->cleanvar($_GET);
        $_POST = $this->cleanvar($_POST);
        $_SESSION = $this->cleanvar($_SESSION);
        $this->checkToken = true; //$this->checkToken = $this->checkToken();
        $this->token = $this->generateToken();
        $this->checkCaptcha = true; //$this->checkCaptcha();
        $this->loginCheck = $this->loginCheck();
        $this->adminCheck = $this->adminCheck();
        if(isset($_GET['err']) && $_GET['err'] != '') {
            $this->err = '<div class="err">' . $_GET['err'] . '</div>';
        }
        if(isset($_GET['suc']) && $_GET['suc'] != '') {
            $this->suc = '<div class="suc">' . $_GET['suc'] . '</div>';
        }
        $this->redirect();
    }
    public function cleanvar($var) {
        $array = array();
        if(is_array($var)) {
            foreach($var as $key => $value) {
                $array[$key] = $this->cleanvar($value);
            }
        } else {
            return trim(nl2br(preg_replace('/\s+/', ' ', $var)));
        }
        return $array;
    }
    /* START - Login functions */
    private function loginHandler() {
        $err = '¡Error!';
        if($this->checkToken) {
            if($this->checkCaptcha) {
                $email = isset($_POST['email']) && filter_var($_POST['email'], FILTER_VALIDATE_EMAIL) ? $_POST['email'] : '';
                $password = SHA1(isset($_POST['password']) ? $_POST['password'] : '');
                if ($stmt = $this->mysqli->prepare('SELECT user.iduser, user.fname, user.lname, user.passdb, user.status, profile.idprofile, profile.profile FROM user LEFT JOIN profile ON profile.idprofile = user.idprofile WHERE user.email = ? LIMIT 1')) {
                    $stmt->bind_param('s', $email);
                    $stmt->execute();
                    $stmt->store_result();
                    $stmt->bind_result($iduser, $fname, $lname, $passdb, $status, $idprofile, $profile);
                    $stmt->fetch();
                    if ($stmt->num_rows == 1) {
                        if ($this->checkBrute($iduser)) {
                            $err .= 'Ha hecho mas de ' . $this->maxLoginAttemps . ' intentos de inicio de sesión en los últimos 30 minutos. Debe esperar una hora para ser habilitado de nuevo o intente recuperar su contraseña.';
                        } else {
                            if (hash_equals($passdb, $password)) {
                                if($status == 1) {
                                    $user_browser = $_SERVER['HTTP_USER_AGENT'] . $this->userIp();
                                    $_SESSION['iduser'] = $iduser;
                                    $_SESSION['email'] = $email;
                                    $_SESSION['fname'] = $fname;
                                    $_SESSION['lname'] = $lname;
                                    $_SESSION['status'] = $status;
                                    $_SESSION['idprofile'] = $idprofile;
                                    $_SESSION['profile'] = $profile;
                                    $_SESSION['stringlogin'] = hash('sha512', $password . $user_browser);
                                    $sql = 'INSERT INTO session(iduser, stringlogin) VALUES (' . $iduser . ', "' . $_SESSION['stringlogin'] . '")';
                                    $this->mysqli->query($sql) or exit($this->mysqli->error);
                                    $this->header('index.php', '?suc=Inicio de sesión correcto.');
                                } else {
                                    $err .= 'Su usuario ha sido desactivado. Contacte al administrador.';
                                }
                            } else {
                                $this->mysqli->query('INSERT INTO trysession(iduser) VALUES ("' . $iduser . '")') or exit($this->mysqli->error);
                                $err .= 'La contraseña no es correcta. Intente de nuevo.';
                            }
                        }
                    } else {
                        $err .= 'El correo electrónico no esta registrado.';
                    }
                } else {
                    $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
                }
            } else {
                $err .= 'El código de la imagen no es correcto. Intente de nuevo.';
            }
        } else {
            $err .= 'La validación del formulario no ha sido exitosa. Intente de nuevo.';
        }
        $this->header('', '?err=' . $err);
    }
    private function logoutHandler() {
        if($stmt = $this->mysqli->prepare('SELECT idsesion FROM session WHERE iduser = ? AND timeend IS NULL')) {
            $stmt->bind_param('i', $this->iduser);
            $stmt->execute();
            $stmt->store_result();
            if ($stmt->num_rows >= 1) {
                $stmt->bind_result($idsesion);
                while($stmt->fetch()) {
                    $this->mysqli->query('UPDATE session SET timeend = now() WHERE iduser = ' . $this->iduser) or exit($this->mysqli->error);
                }
            }
            $stmt->close();
        } else {
            $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
        }
        $_SESSION = array();
        session_unset();
        session_destroy(); 
        $this->header('index.php', '?suc=Ha cerrado sesión.' . (isset($err) ? '&err=' . $err : ''));
    }
    private function recoverPassword() {
        $err = '¡Error!';
        if($this->checkToken) {
            $email = isset($_POST['email']) && filter_var($_POST['email'], FILTER_VALIDATE_EMAIL) ? $_POST['email'] : '';
            $codigo = isset($_POST['codigo']) ? hash('sha512', $_POST['codigo']) : 'null';
            $pass = sha1(isset($_POST['pass']) ? $_POST['pass'] : 'null');
            $passver = sha1(isset($_POST['passver']) ? $_POST['passver'] : 'null2');
            if($email != '') {
                if ($stmt = $this->mysqli->prepare('SELECT iduser, status FROM user WHERE email = ? LIMIT 1')) {
                    $stmt->bind_param('s', $email);
                    $stmt->execute();
                    $stmt->store_result();
                    $stmt->bind_result($iduser, $status);
                    $stmt->fetch();
                    if ($stmt->num_rows == 1) {
                        if($status == 1) {
                            if($codigo != 'null') {
                                if($stmt = $this->mysqli->prepare('SELECT secret FROM recoverpassword WHERE iduser = ? AND status = 1 ORDER BY time DESC')) {
                                    $stmt->bind_param('i', $iduser);
                                    $stmt->execute();
                                    $stmt->store_result();
                                    $stmt->bind_result($secret);
                                    $stmt->fetch();
                                    if(hash_equals($codigo, $secret)) {
                                        if($pass == $passver) {
                                            $sql = 'UPDATE user SET passdb = "' . $pass . '" WHERE iduser = ' . $iduser;
                                            $this->mysqli->query($sql) or exit($this->mysqli->error);
                                            $sql = 'UPDATE recoverpassword SET status = 0 WHERE iduser = ' . $iduser;
                                            $this->mysqli->query($sql) or exit($this->mysqli->error);
                                            $suc = 'Ha cambiado su contraseña, inicie sesión.';
                                        }
                                        $err .= 'Las contraseñas no coinciden, recupere su contraseña de nuevo.';
                                    }
                                } else {
                                    $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
                                }
                            } else {
                                $secret = mt_rand(100001, 999999);
                                $hashsecret = hash('sha512', $secret);
                                $sql = 'INSERT INTO recoverpassword(iduser, secret) VALUES (' . $iduser . ', "' . $hashsecret . '")';
                                $this->mysqli->query($sql) or exit($this->mysqli->error);
                                $subject = $this->title . ' - Recuperar contraseña';
                                $message = '<html><head><title>' . $subject . '</title></head><body><p>Ha solicitado recuperar su contraseña. Ingrese el código inferior en la pagina para continuar:<br /><br /><strong>'.$secret.'</strong><br /><br />' . $this->title . '</p></body></html>';
                                $headers = "MIME-Version: 1.0" . "\r\n";
                                $headers .= "Content-type:text/html;charset=UTF-8" . "\r\n";
                                $headers .= 'From: <' . $this->mail_email . '>' . "\r\n";
                                $headers .= 'Cc: ' . $this->mail_email . "\r\n";
                                sendemail($this, $email, 'Recuperar contraseña', $message, '', 'setPassword&suc=Verifique su correo e ingrese el codigo enviado');
                            }
                        } else {
                            $err .= 'Su usuario ha sido desactivado. Contacte al administrador.';
                        }
                    } else {
                        $err .= 'El correo electrónico no esta registrado. Intente de nuevo.';
                    }
                } else {
                    $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
                }
            }
        } else {
            $err .= 'La validación del formulario no ha sido exitosa. Intente de nuevo.';
        }
        $sql = 'UPDATE recoverpassword SET status = 0 WHERE iduser = ' . $iduser;
        $this->mysqli->query($sql) or exit($this->mysqli->error);
        if(isset($suc)) {
            $this->header('login.php', '?suc=' . $suc);
        }
        $this->header('', '?err=' . $err);
    }
    private function checkBrute($iduser) {
        $intentosvalidos = time() - (2 * 60 * 60);
        if ($stmt = $this->mysqli->prepare('SELECT time FROM trysession WHERE iduser = ? AND time > ?')) {
            $stmt->bind_param('is', $iduser, $intentosvalidos);
            $stmt->execute();
            $stmt->store_result();
            if ($stmt->num_rows > $this->maxLoginAttemps) {
                return true;
            }
        } else {
            $this->header('', '?err=' . html_entity_decode(__LINE__ . ': ' . $this->mysqli->error));
        }
        return false;
    }
    private function adminCheck() {
        if($this->loginCheck) {
            echo  '<div style="display:none;">SELECT admin FROM user WHERE iduser = '.$this->iduser.'</div>';
            if($stmt = $this->mysqli->prepare('SELECT admin FROM user WHERE iduser = ?')) {
                $stmt->bind_param('i', $this->iduser);
                $stmt->execute();
                $stmt->store_result();
                $stmt->bind_result($admin);
                $stmt->fetch();
                if ($stmt->num_rows == 1 && $admin == 1) {
                    return true;
                }
            } else {
                $this->header('', '?err=' . html_entity_decode(__LINE__ . ': ' . $this->mysqli->error));
            }
        }
        return false;
    }
    private function loginCheck() {
        if(isset($_SESSION['iduser'], $_SESSION['email'], $_SESSION['fname'], $_SESSION['lname'], $_SESSION['status'], $_SESSION['stringlogin'])  && $_SESSION['iduser'] != '' && $_SESSION['email'] != '' && $_SESSION['fname'] != '' && $_SESSION['lname'] != '' && $_SESSION['status'] != '' && $_SESSION['stringlogin'] != '') {
            $this->iduser = $_SESSION['iduser'];
            $this->email = $_SESSION['email'];
            $this->fname = $_SESSION['fname'];
            $this->lname = $_SESSION['lname'];
            $this->status = $_SESSION['status'];
            $this->stringlogin = $_SESSION['stringlogin'];
            $user_browser = $_SERVER['HTTP_USER_AGENT'] . $this->userIp();
            return true;
            /*if($stmt = $this->mysqli->prepare('SELECT user.fname, user.lname, user.passdb, user.status, profile.idprofile, profile.profile FROM user JOIN session ON user.iduser = session.iduser LEFT JOIN profile ON user.idprofile = profile.idprofile WHERE user.iduser = ? AND session.stringlogin = ? AND session.timeend IS NULL')) {
                $stmt->bind_param('is', $this->iduser, $this->stringlogin);
                $stmt->execute();
                $stmt->store_result();
                if ($stmt->num_rows > 0) {
                    $stmt->bind_result($fname, $lname, $passdb, $status, $idprofile, $profile);
                    $stmt->fetch();
                    $login_check = hash('sha512', $passdb . $user_browser);
                    if($status == 1) {
                        if(hash_equals($login_check, $this->stringlogin)) {
                            $_SESSION['fname'] = $fname;
                            $_SESSION['lname'] = $lname;
                            $_SESSION['idprofile'] = $idprofile;
                            $_SESSION['profile'] = $profile;
                            return true;
                        }
                    }
                }
            } else {
                $this->header('', '?err=' . html_entity_decode(__LINE__ . ': ' . $this->mysqli->error));
            }*/
        }
        unset($this->iduser, $this->email, $this->fname, $this->lname, $this->admin, $this->status, $this->stringlogin);
        return false;
    }
    private function register() {
        $suc = '';
        $err = '';
        $iduser = isset($_POST['iduser']) ? (int) $_POST['iduser'] : 0;
        if($this->adminCheck) {
            $iduser = $iduser;
        } else if($this->loginCheck) {
            $iduser = $_SESSION['iduser'];
        } else {
            $iduser = 0;
        }
        $email = isset($_POST['email']) && filter_var($_POST['email'], FILTER_VALIDATE_EMAIL) ? $_POST['email'] : '';
        $emailalt = isset($_POST['emailalt']) && filter_var($_POST['emailalt'], FILTER_VALIDATE_EMAIL) ? $_POST['emailalt'] : '';
        $pass = SHA1(isset($_POST['pass']) ? $_POST['pass'] : 'null');
        $passver = SHA1(isset($_POST['passver']) ? $_POST['passver'] : 'null2');
        $fname = isset($_POST['fname']) ? $_POST['fname'] : '';
        $lname = isset($_POST['lname']) ? $_POST['lname'] : '';
        $idtype_id = isset($_POST['idtype_id']) ? $_POST['idtype_id'] : '';
        $status = $this->adminCheck && isset($_POST['status']) ? $_POST['status'] : '';
        $admin = $this->adminCheck && isset($_POST['admin']) ? (int) $_POST['admin'] : 0;
        $adminver = $this->adminCheck && isset($_POST['adminver']) && $admin == 1 ? (int) $_POST['adminver'] : 0;
        $number_id = isset($_POST['number_id']) ? $_POST['number_id'] : '';
        $institution = isset($_POST['institution']) ? $_POST['institution'] : '';
        $position = isset($_POST['position']) ? $_POST['position'] : '';
        $celular = isset($_POST['celular']) ? $_POST['celular'] : '';
        $telefono = isset($_POST['telefono']) ? $_POST['telefono'] : '';
        $idprofile = $this->adminCheck && isset($_POST['idprofile']) ? (int) $_POST['idprofile'] : 0;
        if($this->checkToken) {
            if($this->checkCaptcha) {
                if($email != '' && $fname != '' && $lname != '' && $idtype_id != '' && $number_id != '') {
                    if($stmt = $this->mysqli->prepare('SELECT email FROM user WHERE email = ?')) {
                        $stmt->bind_param('s', $email);
                        $stmt->execute();
                        $stmt->store_result();
                        if($stmt->num_rows == 0 || $iduser > 0) {
                            if($iduser > 0) {
                                if($stmt = $this->mysqli->prepare("UPDATE user SET emailalt = ?, fname = ?, lname = ?, idtype_id = ?, number_id = ?, institution = ?, position = ?, celular = ?, telefono = ?, idprofile = ? WHERE iduser = ?")) {
                                    $stmt->bind_param("sssiissisii", $emailalt, $fname, $lname, $idtype_id, $number_id, $institution, $position, $celular, $telefono, $idprofile, $iduser);
                                    $stmt->execute();
                                    if($this->adminCheck) {
                                        if($stmt = $this->mysqli->prepare("UPDATE user SET status = ?, admin = ? WHERE iduser = ?")) {
                                            $stmt->bind_param("iii", $status, $adminver, $iduser);
                                            $stmt->execute();
                                            $suc .= 'El usuario ha sido editado';
                                        } else {
                                            $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
                                        }
                                    } else {
                                        $suc .= 'Su usuario ha sido editado';
                                    }
                                } else {
                                    $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
                                }
                                $stepverificationArray = array();
                                foreach($this->subjects() as $idsubject => $dataUser) {
                                    $stepverificationArray[$idsubject] = 0;
                                    $sql = 'SELECT stepverification FROM usersubject WHERE iduser = ' . $iduser . ' AND idsubject = ' . $idsubject;
                                    $consulta = $this->mysqli->query($sql) or $this->header('index.php', '?err=' . html_entity_decode(__LINE__ . ': ' . $this->mysqli->error));
                                    while($resultado = $consulta->fetch_array()) {
                                        $stepverificationArray[$idsubject] = $resultado['stepverification'];
                                    }
                                }
                                if($stmt = $this->mysqli->prepare('DELETE FROM usersubject WHERE iduser = ?')) {
                                    $stmt->bind_param('i', $iduser);
                                    $stmt->execute();
                                    $idsubjectcont = $this->adminCheck && isset($_POST['idsubjectcont']) ? (int) $_POST['idsubjectcont'] : 0;
                                    for($cont=1; $cont<=$idsubjectcont; $cont++) {
                                        $idsubject = isset($_POST['idsubject' . $cont]) ? (int) $_POST['idsubject' . $cont] : 0;
                                        if($idsubject > 0) {
                                            $stepverification = isset($stepverificationArray[$idsubject]) ? $stepverificationArray[$idsubject] : 0;
                                            if($stmt = $this->mysqli->prepare('INSERT INTO usersubject(iduser, idsubject, stepverification) VALUES(?, ?, ?)')) {
                                                $stmt->bind_param('iii', $iduser, $idsubject, $stepverification);
                                                $stmt->execute();
                                                $stmt->store_result();
                                            } else {
                                                $err .= html_entity_decode($this->mysqli->error);
                                            }
                                        }
                                    }
                                } else {
                                    $err .= html_entity_decode($this->mysqli->error);
                                }
                            } else if($pass == $passver) {
                                if($stmt = $this->mysqli->prepare("INSERT INTO user(email, emailalt, passdb, fname, lname, idtype_id, number_id, institution, position, celular, telefono) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")) {
                                    $stmt->bind_param("sssssiissis", $email, $emailalt, $pass, $fname, $lname, $idtype_id, $number_id, $institution, $position, $celular, $telefono);
                                    $stmt->execute();
                                    $iduser = $stmt->insert_id;
                                    $type_arr = $this->getvalues('type_id', $idtype_id);
                                    $subject = $this->title . ' - Registro nuevo';
                                    $message = '<html><head><title>' . $subject . '</title></head><body><p>Nuevo registro en ' . $this->title . '. Ingrese a su cuenta para administrar el usuario.<br /><strong>Nombres:</strong> ' . $fname .' ' . $lname .'<br /><strong>Correo electronico:</strong> ' . $email . '<br /><strong>Identificación</strong>: ' . $type_arr[$idtype_id]['value'] . ': ' . $number_id . '<br /><strong>Institución:</strong> ' . $institution . '<br /><strong>Cargo:</strong> ' . $position . '<br /><br />' . $this->title . '</p></body></html>';
                                    $headers = "MIME-Version: 1.0" . "\r\n";
                                    $headers .= "Content-type:text/html;charset=UTF-8" . "\r\n";
                                    $headers .= 'From: <' . $this->mail_email . '>' . "\r\n";
                                    $headers .= 'Cc: ' . $email . "\r\n";
                                    sendemail($this, $this->mail_cc, 'Registro nuevo', $message, 'login.php', 'suc=Registro exitoso, el Administrador validara la información y hará la activación de su usuario.');
                                } else {
                                    $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
                                }
                            } else {
                                $err .= 'La contraseña no coincide. Intente de nuevo.';
                            }
                        } else {
                            $err .= 'El correo electrónico ya esta registrado.';
                        }
                    } else {
                        $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
                    }
                } else {
                    $err .= 'Todos los campos son obligatorios. Intente de nuevo.';
                }
            } else {
                $err .= 'El código de la imagen no es correcto. Intente de nuevo.';
            }
        } else {
            $err .= 'La validación del formulario no ha sido exitosa. Intente de nuevo.';
        }
        if($err == '') {
            $url = $this->adminCheck() ? 'admin-user.php' : 'index.php';
            $this->header($url, '?suc=' . $suc); 
        } else {
            $this->header('index.php', '?err=' . $err); 
        }
    }
    /* END - Login functions */
    /* START - Form functions */
    public function formOpen($action = '') {
        return '<form action="' . $action . '" method="post" enctype="multipart/form-data"><input type="hidden" name="token" value="' . $this->token . '">';
    }
    public function formClose() {
        return '</form>';
    }
    protected function generateToken() {
        $this->token = hash('sha512', openssl_random_pseudo_bytes(16));
        $_SESSION['token'] = $this->token;
        return $this->token;
    }
    protected function checkToken() {
        if(isset($_SESSION['token'], $_POST['token']) && $_SESSION['token'] == $_POST['token']) {
            return true;
        }
        return false;
    }
    protected function userIp() {
        $ipaddress = '100.000.000';
        /*if (getenv('HTTP_CLIENT_IP')) {
            $ipaddress = getenv('HTTP_CLIENT_IP');
        } else if(getenv('HTTP_X_FORWARDED_FOR')) {
            $ipaddress = getenv('HTTP_X_FORWARDED_FOR');
        } else if(getenv('HTTP_X_FORWARDED')) {
            $ipaddress = getenv('HTTP_X_FORWARDED');
        } else if(getenv('HTTP_FORWARDED_FOR')) {
            $ipaddress = getenv('HTTP_FORWARDED_FOR');
        } else if(getenv('HTTP_FORWARDED')) {
            $ipaddress = getenv('HTTP_FORWARDED');
        } else if(getenv('REMOTE_ADDR')) {
            $ipaddress = getenv('REMOTE_ADDR');
        } else {
            $ipaddress = 'UNKNOWN';
        }*/
        return $ipaddress;
    }
    private function checkCaptcha() {
        /*$captcha = isset($_POST['captcha']) ? hash('sha512', $this->mysqli->real_escape_string($_POST['captcha'])) : '';
        if(isset($_SESSION['captcha']) && hash_equals($_SESSION['captcha'], $captcha)) {*/
            return true;
        /*}
        return false;*/
    }
    /* END - Form functions */
    public function getvalues($data, $id = 0) {
        $data = $this->cleanvar($data);
        $return = array();
        if($stmt = $this->mysqli->prepare('SELECT * FROM ' . $data . ($id > 0 ? ' WHERE id' . $data . ' = ?' : ''))) {
            if($id > 0) {
                $stmt->bind_param('i', $id);
            }
            $stmt->execute();
            $stmt->store_result();
            if ($stmt->num_rows > 0) {
                $stmt->bind_result($id, $text, $status);
                while ($stmt->fetch()) {
                    $return[$id] = array('value' => $text, 
                                        'status' => $status);
                }
            }
        } else {
            $this->header('index.php', '?err=' . html_entity_decode(__LINE__ . ': ' . $this->mysqli->error));
        }
        return $return;
    }
    public function users($iduser = -1) {
        $return = array();
        if($stmt = $this->mysqli->prepare('SELECT user.iduser, user.fname, user.lname, user.idtype_id, type_id.type_id, user.number_id, user.email, user.emailalt, user.status, user.admin, user.created, user.institution, user.position, profile.idprofile, profile.profile FROM user LEFT JOIN type_id ON type_id.idtype_id = user.idtype_id LEFT JOIN profile ON profile.idprofile = user.idprofile ' . ($iduser > -1 ? ' WHERE user.iduser = ? ' : '') . ' ORDER BY user.fname ASC')) {
            if($iduser > -1) {
                $stmt->bind_param('i', $iduser);
            }
            $stmt->execute();
            $stmt->store_result();
            if ($stmt->num_rows > 0) {
                $stmt->bind_result($iduser, $fname, $lname, $idtype_id, $type_id, $number_id, $email, $emailalt, $status, $admin, $created, $institution, $position, $idprofile, $profile);
                while ($stmt->fetch()) {
                    $return[$iduser] = array('fname' => $fname,
                                            'lname' => $lname,
                                            'idtype_id' => $idtype_id,
                                            'type_id' => $type_id,
                                            'number_id' => $number_id,
                                            'email' => $email,
                                            'emailalt' => $emailalt,
                                            'status' => $status,
                                            'admin' => $admin,
                                            'created' => $created,
                                            'institution' => $institution,
                                            'position' => $position,
                                            'subject' => array(),
                                            'idprofile' => $idprofile,
                                            'profile' => $profile);
                    $sql = 'SELECT subject.idsubject, subject.subject FROM usersubject JOIN user ON user.iduser = usersubject.iduser JOIN subject ON subject.idsubject = usersubject.idsubject WHERE user.iduser = ' . $iduser;
                    $query = $this->mysqli->query($sql) or $this->header('index.php', '?err=' . html_entity_decode(__LINE__ . ': ' . $this->mysqli->error));
                    while($result = $query->fetch_array()) {
                        $return[$iduser]['subject'][$result['idsubject']] = $result['subject'];
                    }
                }
            }
        } else {
            $this->header('index.php', '?err=' . html_entity_decode(__LINE__ . ': ' . $this->mysqli->error));
        }
        return $return;
    }
    public function subjects($idsubject = -1) {
        $return = array();
        if($stmt = $this->mysqli->prepare('SELECT idsubject, subject, profile, status, created FROM subject ' . ($idsubject > -1 ? ' WHERE idsubject = ? ' : '') . ' ORDER BY subject ASC')) {
            if($idsubject > -1) {
                $stmt->bind_param('i', $idsubject);
            }
            $stmt->execute();
            $stmt->store_result();
            if($stmt->num_rows > 0) {
                $stmt->bind_result($idsubject, $subject, $profile, $status, $created);
                while ($stmt->fetch()) {
                    $return[$idsubject] = array('subject' => $subject,
                                                'profile' => $profile,
                                                'status' => $status,
                                                'created' => $created);
                }
            }
        } else {
            $this->header('index.php', '?err=' . html_entity_decode(__LINE__ . ': ' . $this->mysqli->error));
        }
        return $return;
    }
    public function checkUserSubject($iduser = 0, $idsubject = 0) {
        $return = false;
        if($stmt = $this->mysqli->prepare('SELECT iduser, idsubject FROM usersubject WHERE iduser = ? AND idsubject = ?')) {
            $stmt->bind_param('ii', $iduser, $idsubject);
            $stmt->execute();
            $stmt->store_result();
            if($stmt->num_rows > 0) {
                $return = true;
            }
        } else {
            $this->header('index.php', '?err=' . html_entity_decode(__LINE__ . ': ' . $this->mysqli->error));
        }
        return $return;
    }
    public function profiles($idprofile = -1) {
        $return = array();
        if($stmt = $this->mysqli->prepare('SELECT idprofile, profile, created FROM profile ' . ($idprofile > -1 ? ' WHERE idprofile = ? ' : '') . ' ORDER BY profile ASC')) {
            if($idprofile > -1) {
                $stmt->bind_param('i', $idprofile);
            }
            $stmt->execute();
            $stmt->store_result();
            if($stmt->num_rows > 0) {
                $stmt->bind_result($idprofile, $profile, $created);
                while ($stmt->fetch()) {
                    $return[$idprofile] = array('profile' => $profile,
                                                'created' => $created);
                }
            }
        } else {
            $this->header('index.php', '?err=' . html_entity_decode(__LINE__ . ': ' . $this->mysqli->error));
        }
        return $return;
    }
    public function checkSubjectQuestionAnswer($idsubject = 0, $idsubjectquestion = 0, $idsubjectquestionanswer = 0) {
        $return = false;
        if($stmt = $this->mysqli->prepare('SELECT subject.idsubject FROM subject JOIN subjectquestion ON subjectquestion.idsubject = subject.idsubject JOIN subjectquestionanswer ON subjectquestionanswer.idsubjectquestion = subjectquestion.idsubjectquestion WHERE subjectquestion.idsubject = ? AND subjectquestionanswer.idsubjectquestion = ? AND subjectquestionanswer.idsubjectquestionanswer = ?')) {
            $stmt->bind_param('iii', $idsubject, $idsubjectquestion, $idsubjectquestionanswer);
            $stmt->execute();
            $stmt->store_result();
            if($stmt->num_rows == 1) {
                $return = true;
            }
        } else {
            $this->header('index.php', '?err=' . html_entity_decode(__LINE__ . ': ' . $this->mysqli->error));
        }
        return $return;
    }
    public function site() {
        $return = array('idsite' => 0, 'title' => '');
        if($stmt = $this->mysqli->prepare('SELECT idsite, title FROM site ORDER BY idsite DESC LIMIT 1')) {
            $stmt->execute();
            $stmt->store_result();
            if($stmt->num_rows > 0) {
                $stmt->bind_result($idsite, $title);
                while ($stmt->fetch()) {
                    $return = array('idsite' => $idsite,
                                    'title' => $title);
                }
            }
        } else {
            $this->header('index.php', '?err=' . html_entity_decode(__LINE__ . ': ' . $this->mysqli->error));
        }
        return $return;
    }
    public function subjectquestion($idsubject = 0) {
        $return = array();
        if($stmt = $this->mysqli->prepare('SELECT idsubjectquestion, title, question, hint, type, graph, status FROM subjectquestion WHERE idsubject = ? ORDER BY sortorder ASC')) {
            $stmt->bind_param('i', $idsubject);
            $stmt->execute();
            $stmt->store_result();
            if($stmt->num_rows > 0) {
                $stmt->bind_result($idsubjectquestion, $title, $question, $hint, $type, $graph, $status);
                while ($stmt->fetch()) {
                    $return[$idsubjectquestion] = array('title' => $title,
                                                        'question' => $question,
                                                        'hint' => $hint,
                                                        'type' => $type,
                                                        'graph' => $graph,
                                                        'status' => $status);
                }
            }
        } else {
            $this->header('index.php', '?err=' . html_entity_decode(__LINE__ . ': ' . $this->mysqli->error));
        }
        return $return;
    }
    public function subjectquestionanswer($idsubjectquestion = 0, $form = false) {
        $return = '';
        $returnSelected = '';
        $returnSortValue = '';
        $sort = false;
        $twobox = false;
        $onetable = false;
        $onetablece = false;
        $onetablecg = false;

        if($stmt = $this->mysqli->prepare('SELECT subjectquestionanswer.idsubjectquestionanswer, subjectquestionanswer.answer,subjectquestionanswer.sortorder, subjectquestion.type, subjectquestionanswer.readonly, subjectquestion.idsubject FROM subjectquestion LEFT JOIN subjectquestionanswer ON subjectquestion.idsubjectquestion = subjectquestionanswer.idsubjectquestion WHERE subjectquestion.idsubjectquestion = ? ORDER BY subjectquestionanswer.sortorder, subjectquestionanswer.answer ASC')) {
            $stmt->bind_param('i', $idsubjectquestion);
            $stmt->execute();
            $stmt->store_result();
            if($stmt->num_rows > 0) {
                $stmt->bind_result($idsubjectquestionanswer, $answer,$sortorder, $type, $readonly, $idsubjectdb);
                $cont = 0;
                $contSort = 0;
                while ($stmt->fetch()) {
                    $cont = $cont < $idsubjectquestionanswer ? $idsubjectquestionanswer :  $cont;
                    if($type == 'checkbox') {
                        $value = 0;
                        $sql = 'SELECT * FROM usersubjectquestionanswer WHERE iduser = ' . $_SESSION['iduser'] . ' AND idsubjectquestion = ' . $idsubjectquestion . ' AND idsubjectquestionanswer = ' . $idsubjectquestionanswer;
                        if($consulta = $this->mysqli->query($sql)) {
                            $value = $consulta->num_rows;
                        }
                        if($form) {
                            if($readonly==1){
                                $return .= '<label><h3>' . $answer. ' </h3></label> <br />';
                            }else if($readonly==2){
                                $return .= '<pre style="display:inline">&#09;</pre><b><label>' . $answer. ' </label></b><br />';
                            }elseif($readonly==0 ){
                            $return .= '<pre style="display:inline">&#09;&#09;</pre><label><input type="checkbox" name="question_' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '" value="1" ' . ($value == 0 ? '' : 'checked') . '> ' . $answer. ' </label><br />';
                        }   
                        } else {
                            if($value != 0) {
                                    if($readonly==1){
                                        $return .= '<label><h3>' . $answer. ' </h3></label> <br />';
                                    }else if($readonly==2){
                                        $return .= '<pre style="display:inline">&#09;</pre><b><label>' . $answer. ' </label></b><br />';
                                    }elseif($readonly==0 ){
                                    $return .= '<pre style="display:inline">&#09;&#09;</pre><label><label><input type="checkbox" checked disabled> ' . $answer . '</label><br />';
                                }   
                            }
                        }
                    } else if($type == 'percent') {
                        $value = 0;
                        $sql = 'SELECT percent FROM usersubjectquestionpercent WHERE iduser = ' . $_SESSION['iduser'] . ' AND idsubjectquestion = ' . $idsubjectquestion . ' AND idsubjectquestionanswer = ' . $idsubjectquestionanswer;
                        if($consulta = $this->mysqli->query($sql)) {
                            $resultados = $consulta->fetch_array();
                            $value = $resultados['percent'];
                        }
                        $return .= '<label>' . $answer;
                        if($form) {
                            $return .= '<select name="percent_' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '" class="percentValues">';
                            $return .= '<option value="0" ' . ($value == 0 ? 'selected' : '') . '>0%</option>';
                            $return .= '<option value="5" ' . ($value == 5 ? 'selected' : '') . '>5%</option>';
                            $return .= '<option value="10" ' . ($value == 10 ? 'selected' : '') . '>10%</option>';
                            $return .= '<option value="15" ' . ($value == 15 ? 'selected' : '') . '>15%</option>';
                            $return .= '<option value="20" ' . ($value == 20 ? 'selected' : '') . '>20%</option>';
                            $return .= '<option value="25" ' . ($value == 25 ? 'selected' : '') . '>25%</option>';
                            $return .= '<option value="30" ' . ($value == 30 ? 'selected' : '') . '>30%</option>';
                            $return .= '<option value="35" ' . ($value == 35 ? 'selected' : '') . '>35%</option>';
                            $return .= '<option value="40" ' . ($value == 40 ? 'selected' : '') . '>40%</option>';
                            $return .= '<option value="45" ' . ($value == 45 ? 'selected' : '') . '>45%</option>';
                            $return .= '<option value="50" ' . ($value == 50 ? 'selected' : '') . '>50%</option>';
                            $return .= '<option value="55" ' . ($value == 55 ? 'selected' : '') . '>55%</option>';
                            $return .= '<option value="60" ' . ($value == 60 ? 'selected' : '') . '>60%</option>';
                            $return .= '<option value="65" ' . ($value == 65 ? 'selected' : '') . '>65%</option>';
                            $return .= '<option value="70" ' . ($value == 70 ? 'selected' : '') . '>70%</option>';
                            $return .= '<option value="75" ' . ($value == 75 ? 'selected' : '') . '>75%</option>';
                            $return .= '<option value="80" ' . ($value == 80 ? 'selected' : '') . '>80%</option>';
                            $return .= '<option value="85" ' . ($value == 85 ? 'selected' : '') . '>85%</option>';
                            $return .= '<option value="90" ' . ($value == 90 ? 'selected' : '') . '>90%</option>';
                            $return .= '<option value="95" ' . ($value == 95 ? 'selected' : '') . '>95%</option>';
                            $return .= '<option value="100" ' . ($value == 100 ? 'selected' : '') . '>100%</option>';
                            $return .= '</select></label><br />';
                        } else {
                            if($value != 0) {
                                $return .= ': ' . $value . '%</label><br />';
                            }
                        }
                    } else if($type == 'sort') {
                        $sort = true;
                        $return .= '<li class="ui-state-default" id="sort_' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '"><span class="ui-icon ui-icon-arrowthick-2-n-s"></span> ' . $answer . '</li>';
                        $returnSortName = 'sort_' . $idsubjectquestion;
                        $returnSortValue .= 'sort_' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '|';
                        $sql = 'SELECT sort FROM usersubjectquestionsort WHERE iduser = ' . $_SESSION['iduser'] . ' AND idsubjectquestion = ' . $idsubjectquestion;
                        if($consulta = $this->mysqli->query($sql)) {
                            $resultados = $consulta->fetch_array();
                            $returnSortValue = $resultados['sort'];
                        }
                    } else if($type == 'compare') {
                        $comment = '';
                        $sql = 'SELECT comment FROM usersubjectquestioncompare WHERE iduser = ' . $_SESSION['iduser'] . ' AND idsubjectquestion = ' . $idsubjectquestion . ' AND idsubjectquestionanswer = ' . $idsubjectquestionanswer;
                        if($consulta = $this->mysqli->query($sql)) {
                            $resultados = $consulta->fetch_array();
                            $comment = $resultados['comment'];
                        }
                        if($form) {
                            $return .= '<span>' . $answer . '</span><textarea name="compare_' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '">' . $comment . '</textarea>';
                        } else {
                            if($comment != '') {
                                $return .= '<span>' . $answer . '</span><br />' . $comment . '';
                            }
                        }
                    } else if($type == 'twobox') {
                        $twobox = true;
                        $selected = '';
                        $sql = 'SELECT twobox FROM usersubjectquestiontwobox WHERE iduser = ' . $_SESSION['iduser'] . ' AND idsubjectquestion = ' . $idsubjectquestion . ' AND twobox LIKE "%twobox_' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '%"';
                        if($consulta = $this->mysqli->query($sql)) {
                            $resultados = $consulta->fetch_array();
                            $selected = $resultados['twobox'];
                        }
                        if($resultados['twobox'] != '') {
                            $returnSelected .= '<option  value="twobox_' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '" selected> ' . $answer . '</option>';
                        } else {
                            $return .= '<option  value="twobox_' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '"> ' . $answer . '</option>';
                        }
                    }
                    else if($type == 'onetable') {
                        //$value = 0;
                        //$onetable = true;
                        $sql = 'SELECT * FROM usersubjectquestiononetable WHERE iduser = ' . $_SESSION['iduser'] . ' AND idsubjectquestion = ' . $idsubjectquestion . ' AND idsubjectquestionanswer = ' . $idsubjectquestionanswer;
                        if($consulta = $this->mysqli->query($sql)) {
                            $resultados = $consulta->fetch_array();
                            $value = $resultados['onetable_realizar'];
                        }
                        if($resultados['onetable_realizar'] != '') {
                                if ($idsubjectdb==5){ 
                                            $return .= ' <tr class="'.$sortorder.'"  id="'.$sortorder.'" style="display: ;" >';
                                }else{                                    
                                    $return .= ' <tr class="'.$sortorder.'"  id="'.$sortorder.'" style="display: ;" >';
                                }
                                    $return .= '<td>'.$answer.'</td>
                                                <td  ALIGN="center"> 
                                                    <select name="onetable_' .$idsubjectquestion . '_' . $idsubjectquestionanswer . '_realizar" id="' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '">
                                                        <option value="">Selecione una</option>
                                                        <option value="Si"' . ( $resultados['onetable_realizar'] == "Si"? 'selected' : '') . '>Si</option>
                                                        <option value="No" ' . ( $resultados['onetable_realizar'] == "No" ? 'selected' : '') . '>No</option>
                                                    </select>
                                                </td>     
                                                <td>
                                                    <select name="onetable_' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '_nivel" id="' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '">
                                                        <option value="">Selecione una</option>
                                                        <option value="Deben conocer"' . ( $resultados['onetable_nivel'] == "Deben conocer"? 'selected' : '') . '>Deben conocer</option>
                                                        <option value="Deben saber interpretar"' . ( $resultados['onetable_nivel'] == "Deben saber interpretar"? 'selected' : '') . '>Deben saber interpretar</option>
                                                        <option value="Deben hacer"' . ( $resultados['onetable_nivel'] == "Deben hacer"? 'selected': '') . '>Deben hacer</option>
                                                        <option value="No aplica"' . ( $resultados['onetable_nivel'] == "No aplica" ? 'selected': '') . '>No aplica</option>
                                                    </select>
                                                </td>
                                                <td>
                                                    <textarea name="onetable_' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '_justificacion" id="' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '">'.$resultados['onetable_justificacion'].'</textarea>
                                                </td>
                                            </tr>
                                        ';
                        } else {
                            if ($idsubjectdb==5){ 
                                    $return .= '
                                            <tr class="'.$sortorder.'"  id="'.$sortorder.'" style="display:none ;" >';
                                    }else{                                    
                                        $return .= '
                                        <tr class="'.$sortorder.'"  id="'.$sortorder.'" style="display: ;" >';
                                    }
                                $return .= '
                                        <td >'. $answer.'</td>
                                        <td ALIGN="center">
                                        <select name="onetable_' .$idsubjectquestion . '_' . $idsubjectquestionanswer . '_realizar" id="' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '">
                                                <option value="">Selecione una</option>
                                                <option value="Si">Si</option>
                                                <option value="No">No</option>
                                            </select>
                                        </td>
                                        <td >
                                        <select name="onetable_' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '_nivel" id="' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '">
                                            <option value="">Selecione una</option>
                                            <option value="Deben conocer">Deben conocer</option>
                                            <option value="Deben saber interpretar">Deben saber interpretar</option>
                                            <option value="Deben hacer">Deben hacer</option>
                                            <option value="No aplica">No aplica</option>
                                        </select></td>
                                        <td >
                                            <textarea name="onetable_' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '_justificacion" id="' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '"></textarea>
                                        </td>
                                    </tr>
                                ';
                                 
                        }
                    }
                    //----- competemcias genericas
                    else if($type == 'onetablecg') {
                        //$value = 0;
                        //$onetable = true;
                        $sql = 'SELECT * FROM usersubjectquestiononetable_cg WHERE iduser = ' . $_SESSION['iduser'] . ' AND idsubjectquestion = ' . $idsubjectquestion . ' AND idsubjectquestionanswer = ' . $idsubjectquestionanswer;
                        if($consulta = $this->mysqli->query($sql)) {
                            $resultados_cg = $consulta->fetch_array();
                            $value = $resultados_cg['onetable_nivel_cg'];
                        }
                        if($resultados_cg['onetable_nivel_cg'] != '') {

                            $select_procedimiento=$sortorder;

                                    $return .= '
                                            <tr class="onetablecg_'.$sortorder.'"  id="onetablecg_'.$sortorder.'" style="display: ;" >
                                                <td>'.$answer.'</td>
                                                <td>
                                                <select name="onetablecg_' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '_nivel" id="' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '">
                                                    <option value="">Selecione una</option>
                                                    <option value="Totalmente en desacuerdo"' . ( $resultados_cg['onetable_nivel_cg'] == "Totalmente en desacuerdo"? 'selected' : '') . '>Totalmente en desacuerdo</option>
                                                    <option value="Ni de acuerdo ni en desacuerdo"' . ( $resultados_cg['onetable_nivel_cg'] == "Ni de acuerdo ni en desacuerdo"? 'selected' : '') . '>Ni de acuerdo ni en desacuerdo</option>
                                                    <option value="De acuerdo"' . ( $resultados_cg['onetable_nivel_cg'] == "De acuerdo"? 'selected': '') . '>De acuerdo</option>
                                                    <option value="Totalmente de acuerdo"' . ( $resultados_cg['onetable_nivel_cg'] == "Totalmente de acuerdo" ? 'selected': '') . '>Totalmente de acuerdo</option>
                                                </select>
                                                </td>
                                                <td>
                                                    <textarea name="onetablecg_' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '_justificacion" id="' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '">'.$resultados_cg['onetable_justificacion_cg'].'</textarea>
                                                </td>
                                            </tr>
                                            &nbsp; 
                                        ';
                        } else {

                                $return .= '
                                    <tr  id="onetablecg_'.$sortorder.'" class="onetablecg_'.$sortorder.'"style="display:none ;" >
                                        <td >'. $answer.'</td>
                                        <td >
                                        <select name="onetablecg_' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '_nivel" id="' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '">
                                            <option value="">Selecione una</option>
                                            <option value="Totalmente en desacuerdo">Totalmente en desacuerdo</option>
                                            <option value="Ni de acuerdo ni en desacuerdo">Ni de acuerdo ni en desacuerdo</option>
                                            <option value="De acuerdo">De acuerdo</option>
                                            <option value="Totalmente de acuerdo">Totalmente de acuerdo</option>
                                        </select></td>
                                        <td >
                                            <textarea name="onetablecg_' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '_justificacion" id="' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '"></textarea>
                                        </td>
                                    </tr>
                                    &nbsp; 
                                ';
                                 
                        }
                    }

                    //----------Competencias especificas
                    else if($type == 'onetablece') {
                        //$value = 0;
                        //$onetable = true;
                        $sql = 'SELECT * FROM usersubjectquestiononetable_ce WHERE iduser = ' . $_SESSION['iduser'] . ' AND idsubjectquestion = ' . $idsubjectquestion . ' AND idsubjectquestionanswer = ' . $idsubjectquestionanswer;
                        if($consulta = $this->mysqli->query($sql)) {
                            $resultados_ce = $consulta->fetch_array();
                            $value = $resultados_ce['onetable_nivel_ce'];
                        }
                        if($resultados_ce['onetable_nivel_ce'] != '') {

                            $select_procedimiento=$sortorder;
                   
                                    $return .= '
                                            <tr class="'.$sortorder.'"  id="'.$sortorder.'" style="display: ;" >';
                 
                                            $return .= ' 
                                            <td>'.$answer.'</td>
                                                <td>
                                                    <select name="onetablece_' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '_nivel" id="' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '">
                                                        <option value="">Selecione una</option>
                                                        <option value="Totalmente en desacuerdo"' . ( $resultados_ce['onetable_nivel_ce'] == "Totalmente en desacuerdo"? 'selected' : '') . '>Totalmente en desacuerdo</option>
                                                    <option value="Ni de acuerdo ni en desacuerdo"' . ( $resultados_ce['onetable_nivel_ce'] == "Ni de acuerdo ni en desacuerdo"? 'selected' : '') . '>Ni de acuerdo ni en desacuerdo</option>
                                                    <option value="De acuerdo"' . ( $resultados_ce['onetable_nivel_ce'] == "De acuerdo"? 'selected': '') . '>De acuerdo</option>
                                                    <option value="Totalmente de acuerdo"' . ( $resultados_ce['onetable_nivel_ce'] == "Totalmente de acuerdo" ? 'selected': '') . '>Totalmente de acuerdo</option>
                                                </select>
                                                </td>
                                                <td>
                                                    <textarea name="onetablece_' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '_justificacion" id="' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '">'.$resultados_ce['onetable_justificacion_ce'].'</textarea>
                                                </td>
                                            </tr>
                                            &nbsp; 
                                            &nbsp; 
                                        ';
                        } else {

                                $return .= '
                                    <tr  id="onetablece_'.$sortorder.'" class="onetablece_'.$sortorder.'"style="display:none ;" >
                                        <td >'. $answer.'</td>
                                        <td >
                                        <select name="onetablece_' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '_nivel" id="' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '">
                                            <option value="">Selecione una</option>
                                            <option value="Totalmente en desacuerdo">Totalmente en desacuerdo</option>
                                            <option value="Ni de acuerdo ni en desacuerdo">Ni de acuerdo ni en desacuerdo</option>
                                            <option value="De acuerdo">De acuerdo</option>
                                            <option value="Totalmente de acuerdo">Totalmente de acuerdo</option>
                                        </select></td>
                                        <td >
                                            <textarea name="onetablece_' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '_justificacion" id="' . $idsubjectquestion . '_' . $idsubjectquestionanswer . '"></textarea>
                                        </td>
                                    </tr>
                                    &nbsp; 
                                ';
                                 
                        }
                    }
                }
            }
        } else {
            $this->header('index.php', '?err=' . html_entity_decode(__LINE__ . ': ' . $this->mysqli->error));
        }
        if($sort) {
            return '<input type="hidden" name="' . $returnSortName . '" id="sortOrder" value="' . $returnSortValue . '"><ul class="sortable" id="' . ($form ? 'sortable' : 'sortableVerification') . '">' . $return . '</ul>';
        } else if($twobox) {
            if($form) {
                $otros = '';
                $sql = 'SELECT twobox FROM usersubjectquestiontwobox WHERE iduser = ' . $_SESSION['iduser'] . ' AND idsubjectquestion = ' . $idsubjectquestion . ' AND twobox NOT LIKE "%twobox%"';
                if($consulta = $this->mysqli->query($sql)) {
                    $resultados = $consulta->fetch_array();
                    $otros = explode('|', $resultados['twobox']);
                }
                $otroInput = '';
                for($a=1; $a<=20; $a++) {
                    $otroInput .= '<tr id="twobox_' . $idsubjectquestion .'_otro' . $a .'"  style="display: ' . ($a == 1 || isset($otros[$a-1]) ? 'flex' : 'none') . ';margin-bottom:10px;"><td style="width: 90%;"><input name="twobox_' . $idsubjectquestion .'_otro[]" value="'.(isset($otros[$a-1]) ? $otros[$a-1] : '').'" placeholder="Otro"></td><td style="vertical-align: top;width: 5%;">' . ($a == 1 ? '<a onclick="cont' . $idsubjectquestion .'++; document.getElementById(\'twobox_' . $idsubjectquestion .'_otro\' + cont' . $idsubjectquestion .').style.display = \'flex\';" class="btn suc" style="text-decoration: none;"> + </a>' : '<a onclick="document.getElementById(\'twobox_' . $idsubjectquestion .'_otro' . $a .'\').style.display = \'none\';" class="btn err" style="text-decoration: none;"> - </a>') . '</td></tr>';
                }
                return '<script>var cont' . $idsubjectquestion .' = 1;</script><div class="form-grp"><div class="form-input">Seleccione y luego de clic en agregar:<br /><select id="twobox_' . $idsubjectquestion .'_list" multiple>' . $return . '</select><input type="button" value="Agregar" onclick="tranferirSelect(\'twobox_' . $idsubjectquestion .'_list\', \'twobox_' . $idsubjectquestion .'\');"></div><div class="form-input">Las que usted dejaria:<br /><select id="twobox_' . $idsubjectquestion .'" name="twobox_' . $idsubjectquestion .'[]" multiple>'.$returnSelected.'</select><input type="button" value="Quitar" onclick="tranferirSelect(\'twobox_' . $idsubjectquestion .'\',\'twobox_' . $idsubjectquestion .'_list\')"></div></div><div class="form-grp"><div class="form-input"><div class="form-grp"><div class="form-input">Si desea incluir otra área que no este en el listado, por favor incluya en este espacio. Para agregar varias solo debe dar clic en el símbolo + (de color verde):</div></div><table id="twobox_' . $idsubjectquestion .'_table" style="width: 99%;">'.$otroInput.'</table></div></div>';
            } else {
                $otros = '';
                $sql = 'SELECT twobox FROM usersubjectquestiontwobox WHERE iduser = ' . $_SESSION['iduser'] . ' AND idsubjectquestion = ' . $idsubjectquestion . ' AND twobox NOT LIKE "%twobox%"';
                if($consulta = $this->mysqli->query($sql)) {
                    $resultados = $consulta->fetch_array();
                    $otros = explode('|', $resultados['twobox']);
                }
                $otroInput = '';
                foreach($otros as $otro) {
                    $otroInput .= $otro != '' ? '<tr style="display: flex;margin-bottom:10px;"><td style="width: 90%;"><input disabled readonly value="'.$otro.'"></td></tr>' : '';
                }
                return '<div class="form-grp"><div class="form-input"><select multiple disabled readonly>' . $return . '</select></div><div class="form-input"><select multiple disabled readonly>'.$returnSelected.'</select></div></div><div class="form-grp"><div class="form-input"><table id="twobox_' . $idsubjectquestion .'_table" style="width: 99%;">'.$otroInput.'</table></div></div>';
            }
        }else if($onetable) {
            if($form) {
                $otros = '';
                $sql = 'SELECT * FROM usersubjectquestiononetable WHERE iduser = ' . $_SESSION['iduser'] . ' AND idsubjectquestion = ' . $idsubjectquestion . ' AND onetable NOT LIKE "%onetable%"';
                if($consulta = $this->mysqli->query($sql)) {
                    $resultados = $consulta->fetch_array();
                    $otros = explode('|', $resultados['onetable']);
                }
                $otroInput = '';
                for($a=1; $a<=20; $a++) {
                    $otroInput .= '<tr id="onetable_' . $idsubjectquestion .'_otro' . $a .'"  style="display: ' . ($a == 1 || isset($otros[$a-1]) ? 'flex' : 'none') . ';margin-bottom:10px;"><td style="width: 90%;"><input name="onetable_' . $idsubjectquestion .'_otro[]" value="'.(isset($otros[$a-1]) ? $otros[$a-1] : '').'" placeholder="Otro"></td><td style="vertical-align: top;width: 5%;">' . ($a == 1 ? '<a onclick="cont' . $idsubjectquestion .'++; document.getElementById(\'onetable_' . $idsubjectquestion .'_otro\' + cont' . $idsubjectquestion .').style.display = \'flex\';" class="btn suc" style="text-decoration: none;"> + </a>' : '<a onclick="document.getElementById(\'onetable_' . $idsubjectquestion .'_otro' . $a .'\').style.display = \'none\';" class="btn err" style="text-decoration: none;"> - </a>') . '</td></tr>';
                }
                
            } else {
                $otros = '';
                $sql = 'SELECT * FROM usersubjectquestiononetable WHERE iduser = ' . $_SESSION['iduser'] . ' AND idsubjectquestion = ' . $idsubjectquestion . ' AND onetable NOT LIKE "%onetable%"';
                if($consulta = $this->mysqli->query($sql)) {
                    $resultados = $consulta->fetch_array();
                    $otros = explode( );
                    $prueba1 = $resultados['iduser'];
                   return '<div class="form-grp" readonly><div class="form-input" readonly>' . $return . '</div><div class="form-input">';
                }
                
            }
        }
        else if($onetablece) {
            if($form) {
                $otros = '';
                $sql = 'SELECT * FROM usersubjectquestiononetable_ce WHERE iduser = ' . $_SESSION['iduser'] . ' AND idsubjectquestion = ' . $idsubjectquestion . '';
                if($consulta = $this->mysqli->query($sql)) {
                    $resultados_ce = $consulta->fetch_array();
                    $otros = explode('|', $resultados_ce['onetable_ce']);
                }
                $otroInput = '';
                for($a=1; $a<=20; $a++) {
                    $otroInput .= '<tr id="onetablece_' . $idsubjectquestion .'_otro' . $a .'"  style="display: ' . ($a == 1 || isset($otros[$a-1]) ? 'flex' : 'none') . ';margin-bottom:10px;"><td style="width: 90%;"><input name="onetablece_' . $idsubjectquestion .'_otro[]" value="'.(isset($otros[$a-1]) ? $otros[$a-1] : '').'" placeholder="Otro"></td><td style="vertical-align: top;width: 5%;">' . ($a == 1 ? '<a onclick="cont' . $idsubjectquestion .'++; document.getElementById(\'onetablece_' . $idsubjectquestion .'_otro\' + cont' . $idsubjectquestion .').style.display = \'flex\';" class="btn suc" style="text-decoration: none;"> + </a>' : '<a onclick="document.getElementById(\'onetablece_' . $idsubjectquestion .'_otro' . $a .'\').style.display = \'none\';" class="btn err" style="text-decoration: none;"> - </a>') . '</td></tr>';
                }
                
            } else {
                $otros = '';
                $sql = 'SELECT * FROM usersubjectquestiononetable_ce WHERE iduser = ' . $_SESSION['iduser'] . ' AND idsubjectquestion = ' . $idsubjectquestion . '';
                if($consulta = $this->mysqli->query($sql)) {
                    $resultados_ce = $consulta->fetch_array();
                    $otros = explode( );
                    $prueba1 = $resultados_ce['iduser'];
                   return '<div class="form-grp" readonly><div class="form-input" readonly>' . $return . '</div><div class="form-input">';
                }
                
            }
        }

        else if($onetablecg) {
            if($form) {
                $otros = '';
                $sql = 'SELECT * FROM usersubjectquestiononetable_cg WHERE iduser = ' . $_SESSION['iduser'] . ' AND idsubjectquestion = ' . $idsubjectquestion . ' AND onetable NOT LIKE "%onetable%"';
                if($consulta = $this->mysqli->query($sql)) {
                    $resultados_cg = $consulta->fetch_array();
                    $otros = explode('|', $resultados_cg['onetable_cg']);
                }
                $otroInput = '';
                for($a=1; $a<=20; $a++) {
                    $otroInput .= '<tr id="onetablecg_' . $idsubjectquestion .'_otro' . $a .'"  style="display: ' . ($a == 1 || isset($otros[$a-1]) ? 'flex' : 'none') . ';margin-bottom:10px;"><td style="width: 90%;"><input name="onetablecg_' . $idsubjectquestion .'_otro[]" value="'.(isset($otros[$a-1]) ? $otros[$a-1] : '').'" placeholder="Otro"></td><td style="vertical-align: top;width: 5%;">' . ($a == 1 ? '<a onclick="cont' . $idsubjectquestion .'++; document.getElementById(\'onetablecg_' . $idsubjectquestion .'_otro\' + cont' . $idsubjectquestion .').style.display = \'flex\';" class="btn suc" style="text-decoration: none;"> + </a>' : '<a onclick="document.getElementById(\'onetablecg_' . $idsubjectquestion .'_otro' . $a .'\').style.display = \'none\';" class="btn err" style="text-decoration: none;"> - </a>') . '</td></tr>';
                }
                
            } else {
                $otros = '';
                $sql = 'SELECT * FROM usersubjectquestiononetable_cg WHERE iduser = ' . $_SESSION['iduser'] . ' AND idsubjectquestion = ' . $idsubjectquestion . ' AND onetable NOT LIKE "%onetable%"';
                if($consulta = $this->mysqli->query($sql)) {
                    $resultados_cg = $consulta->fetch_array();
                    $otros = explode( );
                    $prueba1 = $resultados_cg['iduser'];
                   return '<div class="form-grp" readonly><div class="form-input" readonly>' . $return . '</div><div class="form-input">';
                }
                
            }
        }
        return $return;
    }
    public function checkStepVerification($iduser = 0, $idsubject = 0) {
        $err = '';
        $stepverification = 0;
        if($stmt = $this->mysqli->prepare("SELECT stepverification FROM usersubject WHERE iduser = ? AND idsubject = ?")) {
            $stmt->bind_param("ii", $iduser, $idsubject);
            $stmt->execute();
            $stmt->store_result();
            $stmt->bind_result($stepverification);
            $stmt->fetch();
        } else {
            $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
        }
        return $stepverification == 1 ? true : false;
    }
    public function step1() {
        $err = '';
        $idsubject = isset($_POST['idsubject']) ? (int) $_POST['idsubject'] : 0;
        if($this->checkStepVerification($_SESSION['iduser'], $idsubject)) {
            $this->header('stepverification.php', '?suc=El formulario ha sido enviado y no puede ser modificado.&idsubject=' . $idsubject);
        }
    }
    public function saveUserSubjectQuestionAnswer() {
        $err = '';
        $idsubject = isset($_POST['idsubject']) ? (int) $_POST['idsubject'] : 0;
        if($this->checkUserSubject($_SESSION['iduser'], $idsubject)) {
            if($this->checkStepVerification($_SESSION['iduser'], $idsubject) !== true) {
                $subjects = $this->subjects($idsubject);
                if($subjects[$idsubject]['status'] == 1) {
                    $profilecomment_asistencial = isset($_POST['profilecomment_asistencial']) ? $_POST['profilecomment_asistencial'] : '';
                    $profilecomment_docente = isset($_POST['profilecomment_docente']) ? $_POST['profilecomment_docente'] : '';
                    $profilecomment_investigativo = isset($_POST['profilecomment_investigativo']) ? $_POST['profilecomment_investigativo'] : '';
                    $profilecomment_administrativo = isset($_POST['profilecomment_administrativo']) ? $_POST['profilecomment_administrativo'] : '';
                    if($profilecomment_docente != '' || $profilecomment_investigativo != '' || $profilecomment_administrativo != '' || $profilecomment_asistencial != '') {
                        if($stmt = $this->mysqli->prepare("DELETE FROM usersubjectprofilecomment WHERE iduser = ? AND idsubject = ?")) {
                            $stmt->bind_param("ii", $_SESSION['iduser'], $idsubject);
                            $stmt->execute();
                        } else {
                            $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
                        }
                        if($stmt = $this->mysqli->prepare("INSERT INTO usersubjectprofilecomment(iduser, idsubject, profilecomment_asistencial, profilecomment_docente, profilecomment_investigativo, profilecomment_administrativo) VALUES(?, ?, ?, ?, ?, ?)")) {
                            $stmt->bind_param("iissss", $_SESSION['iduser'], $idsubject, $profilecomment_asistencial, $profilecomment_docente, $profilecomment_investigativo, $profilecomment_administrativo);
                            $stmt->execute();
                        } else {
                            $err .= html_entity_decode(__LINE__ . $stmt->error . $this->mysqli->error);
                        }
                    }
                    foreach($_POST as $key => $value) {
                        $questionData = explode('_', $key);
                        $idsubjectquestion = isset($questionData[1]) ? $questionData[1] : 0;
                        if($stmt = $this->mysqli->prepare("DELETE FROM usersubjectquestionanswer WHERE iduser = ? AND idsubjectquestion = ?")) {
                            $stmt->bind_param("ii", $_SESSION['iduser'], $idsubjectquestion);
                            $stmt->execute();
                        } else {
                            $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
                        }
                        if($stmt = $this->mysqli->prepare("DELETE FROM usersubjectquestioncomment WHERE iduser = ? AND idsubjectquestion = ?")) {
                            $stmt->bind_param("ii", $_SESSION['iduser'], $idsubjectquestion);
                            $stmt->execute();
                        } else {
                            $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
                        }
                        if($stmt = $this->mysqli->prepare("DELETE FROM usersubjectquestioncompare WHERE iduser = ? AND idsubjectquestion = ?")) {
                            $stmt->bind_param("ii", $_SESSION['iduser'], $idsubjectquestion);
                            $stmt->execute();
                        } else {
                            $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
                        }
                        if($stmt = $this->mysqli->prepare("DELETE FROM usersubjectquestionpercent WHERE iduser = ? AND idsubjectquestion = ?")) {
                            $stmt->bind_param("ii", $_SESSION['iduser'], $idsubjectquestion);
                            $stmt->execute();
                        } else {
                            $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
                        }
                        if($stmt = $this->mysqli->prepare("DELETE FROM usersubjectquestionsort WHERE iduser = ? AND idsubjectquestion = ?")) {
                            $stmt->bind_param("ii", $_SESSION['iduser'], $idsubjectquestion);
                            $stmt->execute();
                        } else {
                            $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
                        }
                        if($stmt = $this->mysqli->prepare("DELETE FROM usersubjectquestiontwobox WHERE iduser = ? AND idsubjectquestion = ?")) {
                            $stmt->bind_param("ii", $_SESSION['iduser'], $idsubjectquestion);
                            $stmt->execute();
                        } else {
                            $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
                        }
                       
                    }
                    foreach($_POST as $key => $value) {
                        $questionData = explode('_', $key);
                        $type = isset($questionData[0]) ? $questionData[0] : '';
                        $idsubjectquestion = isset($questionData[1]) ? $questionData[1] : 0;
                        $idsubjectquestionanswer = isset($questionData[2]) ? $questionData[2] : 0;
                        $subtype = isset($questionData[3]) ? $questionData[3] : '';
                        
                        if(is_array($value)){
                            $valueOld = $value;
                            $value = '';
                            foreach($valueOld as $val) {
                                if($val != '') {
                                    $value .= $val . '|';
                                }
                            }
                        }
                        if($value != '') {
                            //echo $type . '<br />' . $idsubjectquestion . '<br />' . $idsubjectquestionanswer . '<br />' . (is_array($value) ? 'Array!' : $value) . '<hr />';
                            if($type == 'question') {
                                if($this->checkSubjectQuestionAnswer($idsubject, $idsubjectquestion, $idsubjectquestionanswer)) {
                                    if($stmt = $this->mysqli->prepare("INSERT INTO usersubjectquestionanswer(iduser, idsubjectquestion, idsubjectquestionanswer) VALUES(?, ?, ?)")) {
                                        $stmt->bind_param("iii", $_SESSION['iduser'], $idsubjectquestion, $idsubjectquestionanswer);
                                        $stmt->execute();
                                    } else {
                                        $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
                                    }
                                }
                            } else if($type == 'comment') {
                                if($value != '') {
                                    if($stmt = $this->mysqli->prepare("INSERT INTO 	usersubjectquestioncomment(iduser, idsubjectquestion, comment) VALUES(?, ?, ?)")) {
                                        $stmt->bind_param("iis", $_SESSION['iduser'], $idsubjectquestion, $value);
                                        $stmt->execute();
                                    } else {
                                        $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
                                    }
                                }
                            } else if($type == 'percent') {
                                if($stmt = $this->mysqli->prepare("INSERT INTO 	usersubjectquestionpercent(iduser, idsubjectquestion, idsubjectquestionanswer, percent) VALUES(?, ?, ?, ?)")) {
                                    $stmt->bind_param("iiii", $_SESSION['iduser'], $idsubjectquestion, $idsubjectquestionanswer, $value);
                                    $stmt->execute();
                                } else {
                                    $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
                                }
                            } else if($type == 'compare') {
                                if($stmt = $this->mysqli->prepare("INSERT INTO 	usersubjectquestioncompare(iduser, idsubjectquestion, idsubjectquestionanswer, comment) VALUES(?, ?, ?, ?)")) {
                                    $stmt->bind_param("iiis", $_SESSION['iduser'], $idsubjectquestion, $idsubjectquestionanswer, $value);
                                    $stmt->execute();
                                } else {
                                    $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
                                }
                            } else if($type == 'sort') {
                                if($stmt = $this->mysqli->prepare("INSERT INTO 	usersubjectquestionsort(iduser, idsubjectquestion, sort) VALUES(?, ?, ?)")) {
                                    $stmt->bind_param("iis", $_SESSION['iduser'], $idsubjectquestion, $value);
                                    $stmt->execute();
                                } else {
                                    $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
                                }
                            } else if($type == 'twobox') {
                                if($stmt = $this->mysqli->prepare("INSERT INTO 	usersubjectquestiontwobox(iduser, idsubjectquestion, twobox) VALUES(?, ?, ?)")) {
                                    $stmt->bind_param("iis", $_SESSION['iduser'], $idsubjectquestion, $value);
                                    $stmt->execute();
                                } else {
                                    $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
                                }
                            }else if($type == 'onetable') { // TODO: Volver dinamico
                                $column = 'onetable_justificacion';
                               
                                if($subtype == 'realizar') {
                                    $column = 'onetable_realizar';
                                    $realizar = $value;
                                } else if($subtype == 'nivel') {
                                    $column = 'onetable_nivel';
                                    $nivel= $value;
                                }else if($subtype == 'justificacion') {
                                    $justificacion ='';
                                    $justificacion=$value;
                                }
                               

                                $num_rows = 0;
                                
                               // if($stmt = $this->mysqli->prepare("SELECT COUNT(*) FROM usersubjectquestiononetable WHERE iduser = ? AND idsubjectquestion = ? AND idsubjectquestionanswer = ?")) {

                                


                                    if($stmt0 = $this->mysqli->prepare("SELECT COUNT(*) FROM usersubjectquestiononetable WHERE iduser = ? AND idsubjectquestion = ? AND idsubjectquestionanswer = ?")) {  
                                        $stmt0->bind_param("iii", $_SESSION['iduser'], $idsubjectquestion, $idsubjectquestionanswer);
                                        
                                        $stmt0->execute();
                                        $stmt0->bind_result($num_rows);
                                        $stmt0->fetch();
                                        $stmt0->close();
    
                                    } 
                                   if($num_rows == 0) {
                                    if($realizar!=''&& $nivel!=''&&$justificacion!=''){

                                    if($stmt1 = $this->mysqli->prepare("INSERT INTO usersubjectquestiononetable(iduser, idsubjectquestion, idsubjectquestionanswer, onetable_realizar, onetable_nivel, onetable_justificacion) VALUES(?, ?, ?, ?, ?, ?)")) {
                                        $stmt1->bind_param("iiisss", $_SESSION['iduser'], $idsubjectquestion, $idsubjectquestionanswer, $realizar, $nivel, $justificacion);
                                        $stmt1->execute();
                                        $stmt1->close();                                   
                                    
                                    } else {
                                        $err .= html_entity_decode(__LINE__ . ': ' . $stmt->error);
                                    }
                                }
                            }
                                 else {
                                    if($stmt2 = $this->mysqli->prepare("UPDATE usersubjectquestiononetable SET $column = ? WHERE iduser = ? AND idsubjectquestion = ? AND idsubjectquestionanswer = ?")) {
                                        $stmt2->bind_param("siii", $value, $_SESSION['iduser'], $idsubjectquestion, $idsubjectquestionanswer);
                                        $stmt2->execute();
                                        $stmt2->close();
                                    } else {
                                        $err .= html_entity_decode(__LINE__ . ': ' . $stmt->error);
                                    }
                                }
                            

                            } else if($type == 'onetablecg') { // TODO: Volver dinamico
                                $column_cg = 'onetable_justificacion_cg';
                               
                             if($subtype == 'nivel') {
                                    $column_cg = 'onetable_nivel_cg';
                                    $nivel_cg ='';
                                    $nivel_cg = $value;
                                }else if($subtype == 'justificacion') {
                                    $justificacion_cg ='';
                                    $justificacion_cg =$value;
                                }
                               
                                $num_rows_cg = 0;

                               // if($stmt = $this->mysqli->prepare("SELECT COUNT(*) FROM usersubjectquestiononetable_cg WHERE iduser = ? AND idsubjectquestion = ? AND idsubjectquestionanswer = ?")) {

                                if($stmt_cg = $this->mysqli->prepare("SELECT COUNT(*) FROM usersubjectquestiononetable_cg WHERE iduser = ? AND idsubjectquestion = ? AND idsubjectquestionanswer = ?")) {  
                                    $stmt_cg->bind_param("iii", $_SESSION['iduser'], $idsubjectquestion, $idsubjectquestionanswer);                                    
                                    $stmt_cg->execute();
                                    $stmt_cg->bind_result($num_rows_cg);
                                    $stmt_cg->fetch();
                                    $stmt_cg->close();

                                } 
                                if($num_rows_cg == 0) {
                                if($nivel_cg !=''&&$justificacion_cg !=''){

                                if($stmt_cg_1 = $this->mysqli->prepare("INSERT INTO usersubjectquestiononetable_cg(iduser, idsubjectquestion, idsubjectquestionanswer, onetable_nivel_cg, onetable_justificacion_cg) VALUES(?, ?, ?, ?, ?)")) {
                                    $stmt_cg_1->bind_param("iiiss", $_SESSION['iduser'], $idsubjectquestion, $idsubjectquestionanswer, $nivel_cg , $justificacion_cg );
                                    $stmt_cg_1->execute();
                                    $stmt_cg_1->close();                                   
                                
                                } else {
                                    $err .= html_entity_decode(__LINE__ . ': ' . $stmt_cg_1->error);
                                }
                                }
                                } else {
                                    if($stmt_cg_2 = $this->mysqli->prepare("UPDATE usersubjectquestiononetable_cg SET $column_cg = ? WHERE iduser = ? AND idsubjectquestion = ? AND idsubjectquestionanswer = ?")) {
                                        $stmt_cg_2->bind_param("siii", $value, $_SESSION['iduser'], $idsubjectquestion, $idsubjectquestionanswer);
                                        $stmt_cg_2->execute();
                                        $stmt_cg_2->close();
                                    } else {
                                        $err .= html_entity_decode(__LINE__ . ': ' . $stmt_cg_2->error);
                                    }
                                }
                        

                            }else if($type == 'onetablece') { // TODO: Volver dinamico
                                    $column_ce = 'onetable_justificacion_ce';
                                    
                                    if($subtype == 'nivel') {
                                        $column_ce = 'onetable_nivel_ce';
                                        $nivel_ce= $value;
                                    }else if($subtype == 'justificacion') {
                                        $justificacion_ce ='';
                                        $justificacion_ce=$value;
                                    }
                                    
    
                                    $num_rows_ce = 0;
    
                                    // if($stmt = $this->mysqli->prepare("SELECT COUNT(*) FROM usersubjectquestiononetable_ce WHERE iduser = ? AND idsubjectquestion = ? AND idsubjectquestionanswer = ?")) {
    
                                    
    
    
                                    if($stmt_ce = $this->mysqli->prepare("SELECT COUNT(*) FROM usersubjectquestiononetable_ce WHERE iduser = ? AND idsubjectquestion = ? AND idsubjectquestionanswer = ?")) {  
                                        $stmt_ce->bind_param("iii", $_SESSION['iduser'], $idsubjectquestion, $idsubjectquestionanswer);
                                        
                                        $stmt_ce->execute();
                                        $stmt_ce->bind_result($num_rows_ce);
                                        $stmt_ce->fetch();
                                        $stmt_ce->close();
    
                                    } 
                                if($num_rows_ce == 0) {
                                    if($nivel_ce!=''&&$justificacion_ce!=''){
                                        
    
                                    if($stmt_ce_1 = $this->mysqli->prepare("INSERT INTO usersubjectquestiononetable_ce(iduser, idsubjectquestion, idsubjectquestionanswer, onetable_nivel_ce, onetable_justificacion_ce) VALUES(?, ?, ?, ?, ?)")) {
                                        $stmt_ce_1->bind_param("iiiss", $_SESSION['iduser'], $idsubjectquestion, $idsubjectquestionanswer, $nivel_ce, $justificacion_ce);
                                        $stmt_ce_1->execute(); 
                                        $stmt_ce_1->close();                                   
                                    
                                    } else {
                                        $err .= html_entity_decode(__LINE__ . ': ' . $stmt_ce_1->error);
                                    }
                              }
                                } else {
                                    if($stmt_ce_2 = $this->mysqli->prepare("UPDATE usersubjectquestiononetable_ce SET $column_ce = ? WHERE iduser = ? AND idsubjectquestion = ? AND idsubjectquestionanswer = ?")) {
                                        $stmt_ce_2->bind_param("siii", $value, $_SESSION['iduser'], $idsubjectquestion, $idsubjectquestionanswer);
                                        $stmt_ce_2->execute();
                                        $stmt_ce_2->close();
                                    } else {
                                        $err .= html_entity_decode(__LINE__ . ': ' . $stmt_ce_2->error);
                                    }
                                }
                            
    
                            }
                        }
                    }
                } else {
                    $err .= 'Especialidad deshabilitada.';
                }
            } else {
                $err .= 'El formulario ya ha sido enviado y no puede ser modificado.';
            }
        }
        if($err == '') {
            $this->header('stepverification.php', '?suc=Los datos han sido guardados. Puede hacer el envió o retomar su progreso despues.&idsubject=' . $idsubject);
        } else {
            $this->header('index.php', '?err=' . $err);
        }
    }
    public function subjectsDocuments($idsubject, $idsubjectdocument = 0) {
        $return = array();
        if($stmt = $this->mysqli->prepare('SELECT subjectdocument.idsubjectdocument, subjectdocument.title, subjectdocument.description, subjectdocument.path, subjectdocument.typepath, subjectdocument.private, subjectdocument.created, subjectdocument.iduser, user.fname, user.lname FROM subjectdocument JOIN user ON user.iduser = subjectdocument.iduser WHERE subjectdocument.idsubject = ?' . ($idsubjectdocument > 0 ? ' AND subjectdocument.idsubjectdocument = ? ' : ''))) {
            if($idsubjectdocument > 0) {
                $stmt->bind_param('ii', $idsubject, $idsubjectdocument);
            } else {
                $stmt->bind_param('i', $idsubject);
            }
            $stmt->execute();
            $stmt->store_result();
            if ($stmt->num_rows > 0) {
                $stmt->bind_result($idsubjectdocument, $title, $description, $path, $typepath, $private, $created, $iduser, $fname, $lname);
                while ($stmt->fetch()) {
                    $return[$idsubjectdocument] = array('title' => $title,
                                                        'description' => $description,
                                                        'path' => $path,
                                                        'typepath' => $typepath,
                                                        'private' => $private,
                                                        'created' => $created,
                                                        'iduser' => $iduser,
                                                        'fname' => $fname,
                                                        'lname' => $lname);
                }
            }
        } else {
            $this->header('index.php', '?err=' . html_entity_decode(__LINE__ . ': ' . $this->mysqli->error));
        }
        return $return;
    }
    public function saveStepVerification() {
        $err = '';
        $idsubject = isset($_POST['idsubject']) ? (int) $_POST['idsubject'] : 0;
        if($this->checkUserSubject($_SESSION['iduser'], $idsubject)) {
            $stepverification = isset($_POST['stepverification']) ? (int) $_POST['stepverification'] : 0;
            if($stepverification == 1) {
                if($stmt = $this->mysqli->prepare("UPDATE usersubject SET stepverification = ? WHERE iduser = ? AND idsubject = ?")) {
                    $stmt->bind_param("iii", $stepverification, $_SESSION['iduser'], $idsubject);
                    $stmt->execute();
                    $user = $this->users($_SESSION['iduser']);
                    $subject = $this->subjects($idsubject);
                    $message = '<html><head><title>' . $this->title . '</title></head><body><p>Reciba un cordial saludo,<br /><br /><em>' . $user[$_SESSION['iduser']]['fname'] . ' ' . $user[$_SESSION['iduser']]['lname'] . '</em> ha hecho el envió del formulario de <em>' . $subject[$idsubject]['subject'] . '</em>. Ingrese al sistema <a href="https://ascofame.org.co/web/delphi/">DELPHI</a> para visualizar la información enviada.<br /><br />Actualización de lineamientos de formación de las especializaciones médico quirúrgicas en Colombia<br />' . $this->title . '</p></body></html>';
                    $headers = "MIME-Version: 1.0" . "\r\n";
                    $headers .= "Content-type:text/html;charset=UTF-8" . "\r\n";
                    $headers .= 'From: <' . $this->mail_email . '>' . "\r\n";
                    $headers .= 'Cc: ' . $this->mail_email . "\r\n";
                    sendemail($this, '', 'Envio de formulario', $message, 'index.php', 'suc=El formulario ha sido enviado');
                } else {
                    $err .= html_entity_decode(__LINE__ . ': ' . $this->mysqli->error);
                }
            }
        }
        if($err == '') {
            $this->header('index.php', '?suc=El formulario ha sido enviado.');
        } else {
            $this->header('index.php', '?err=' . $err);
        }
    }
    public function displayMsgs() {
        if(isset($_GET['err'])) {
            return '<div class="err">' . $_GET['err'] . '</div>';
        }
        if(isset($_GET['suc'])) {
            return '<div class="suc">' . $_GET['suc'] . '</div>';
        }
    }
    private function redirect() {
        if(isset($_GET['loginHandler'])) {
            return $this->loginHandler();
        } else if(isset($_GET['recoverPassword'])) {
            return $this->recoverPassword();
        } else if(isset($_GET['logoutHandler'])) {
            return $this->logoutHandler();
        } else if(isset($_GET['register'])) {
            return $this->register();
        } else if(isset($_GET['step1'])) {
            return $this->step1();
        } else if(isset($_GET['saveUserSubjectQuestionAnswer'])) {
            return $this->saveUserSubjectQuestionAnswer();
        } else if(isset($_GET['saveStepVerification'])) {
            return $this->saveStepVerification();
        }
    }
    public function header($url = 'index.php', $params = '?') {
        /*try {
            header('Location: ' . $url . $params);
        } catch (Exception $e) {
            $e->getMessage();
        }*/
        echo 'Haga <a href="' . $url . $params . '">clic aquí</a> para continuar.';
        echo '<script>window.location = "' . $url . $params . '";</script>';
        exit;
    }
}
$main = new main(); ?>