<?php

include_once'conection.php';


 ini_set('date.timezone','America/Bogota');
    $fecha = date("Y-m-d");
    //$fecha = '2020-10-24';
    $sql = $connection->prepare("SELECT DISTINCT cu,cedula_funcionario, CONCAT(primer_nombre,' ',segundo_nombre,' ',primer_apellido,' ',segundo_apellido) nombre, COUNT(det.cedula_fun) cant
FROM funcionario fun LEFT JOIN det_pre_fun det ON fun.cedula_funcionario = det.cedula_fun AND fecha_quiz = '$fecha' AND jornada = 'pm'
WHERE estado = 1
GROUP BY cu,cedula_funcionario
HAVING  COUNT(det.cedula_fun) < 9
ORDER BY `cant` ASC");
    $sql->execute();
    $rows = $sql->fetchAll(PDO::FETCH_ASSOC); 

   // echo "<pre>"; 
   //      print_r($rows);    
   // echo "<pre>"; 




?>
<table id="tabla_lista" class="table table-striped table-bordered">
              <thead>
                <tr>
                  <!-- <th class="d-none">orden</th> -->
                  <th>cu</th>
                  <th>cedula</th>
                  <th>nombre</th>
                  
                  <th>cant</th>
                  
                  

                  
                  

                </tr>
              </thead>
              <tbody>


                <?php

                $aux_cedula=0;
                $j=1;

                foreach ($rows as $resul) {
                    $cedula = $resul['cedula_funcionario'];
                    $nombre = $resul['nombre'];
                    $cu = $resul['cu'];
                    $cant = $resul['cant'];


                      
                        if ($aux_cedula != $cedula) {
                            $aux_cedula=$cedula;

                            if ($j!=1) {
                                echo "</tr>";
                              }
                              echo "<tr>";
                              $j=0;




                              echo "<td>".$cu."</td>";
                              echo "<td>".$cedula."</td>";
                              echo "<td>".$nombre."</td>";
                              

$sql2 = $connection->prepare("SELECT * FROM det_pre_fun det, preguntas WHERE det.cons_pre = preguntas.cons AND cedula_fun =  $cedula AND fecha_quiz = '$fecha' AND jornada = 'pm' ORDER BY orden asc ");
    $sql2->execute();
    $datos2 = $sql2->fetchAll(PDO::FETCH_ASSOC);


                                $problemas=0;
                            foreach ($datos2 as $value) {

                                $p = $value['resp'];

                                $r = fmod($p,2);


                                if($r==1){
                                    $problemas++;
                                    $var = "Si";
                                       echo "<td style='background: yellow' align='center'>".$var."</td>";  

                                }else{
                                     $var = "No"; 
                                      echo "<td align='center'>".$var."</td>";

                                }                                  


                                
                            }

                             echo "<td align='center'>".$problemas."</td>";
                              

                        } 
                                                           

                }
                 ?>
              </tbody>
        </table>
   
 




