horas_semanais.php

   1 <?php
   2 header ("Pragma: no-cache");                                            // sempre carregar página (não vai ser armazenada no cache http 1,0
   3 header("Cache-Control: no-cache, must-revalidate"); // idem     http 1,1
   4
   5 require_once("Globals.php");
   6 //verifica se o administrador está logado
   7 checkCookie();
   8 // realiza conexão com o banco de dados
   9 conecta();
  10
  11 if(isset($_GET['passado']))
  12 {
  13         $title = "Ver Horas Semanais Passadas";
  14         $campo_sql = "mes_anterior";
  15         $prox_mes = "<td class='hover' onClick=\"document.location.href='?';\"> >> </td>";
  16         $mes_anterior = "";
  17 }
  18 else
  19 {
  20         $title = "Ver Horas Semanais";
  21         $campo_sql = "mes_inicio";
  22         $prox_mes = "";
  23         $mes_anterior = "<td class='hover' onClick=\"document.location.href='?passado';\"> << </td>";
  24 }
  25 ?>
  26 <html>
  27 <head>
  28 <title>Área do Administrador</title>
  29 <link rel="stylesheet" type="text/css" href="estilo.css">
  30 </head>
  31 <body>
  32 <?php
  33         $sql = "SELECT
  34                                 (DATEDIFF(semana_inicio, $campo_sql) div 7)+1 AS num_semana,
  35                                 DATE_FORMAT(config.$campo_sql + INTERVAL 0 WEEK, '%d/%m/%Y') AS iniSem1,
  36                                 DATE_FORMAT(config.$campo_sql + INTERVAL 1 WEEK - INTERVAL 1 DAY, '%d/%m/%Y') AS fimSem1,
  37                                 DATE_FORMAT(config.$campo_sql + INTERVAL 1 WEEK, '%d/%m/%Y') AS iniSem2,
  38                                 DATE_FORMAT(config.$campo_sql + INTERVAL 2 WEEK - INTERVAL 1 DAY, '%d/%m/%Y') AS fimSem2,
  39                                 DATE_FORMAT(config.$campo_sql + INTERVAL 2 WEEK, '%d/%m/%Y') AS iniSem3,
  40                                 DATE_FORMAT(config.$campo_sql + INTERVAL 3 WEEK - INTERVAL 1 DAY, '%d/%m/%Y') AS fimSem3,
  41                                 DATE_FORMAT(config.$campo_sql + INTERVAL 3 WEEK, '%d/%m/%Y') AS iniSem4,
  42                                 DATE_FORMAT(config.$campo_sql + INTERVAL 4 WEEK - INTERVAL 1 DAY, '%d/%m/%Y') AS fimSem4
  43                         FROM config";
  44         $rs = mysql_query($sql);
  45         ?>
  46
  47         <center><h2><?php echo $title ." (". mysql_result($rs, 0, 'iniSem1') ." - ". mysql_result($rs, 0, 'fimSem4') .")";  ?></h2></center>
  48
  49         <?php
  50
  51         $select = array();
  52                 for($i=1;$i<5;$i++)
  53                 {
  54                         $col = "tempoSem". $i;
  55                         $select[] = "IF( $col is NULL, '<span class=\'red\'>00h 00min</span>', (IF($col < '06:00:00', TIME_FORMAT($col, '<span class=\'red\'>%Hh %imin</span>'), TIME_FORMAT($col, '%Hh %imin')))) AS $col";
  56                 }
  57                  $sql = "SELECT login,
  58                                                 ". implode($select, ', ') ."
  59                                 FROM
  60                                                 (
  61                                                         (SELECT id_user, login
  62                                                                 FROM usuario) AS usuarios
  63                                                 LEFT JOIN
  64                                                         (SELECT id_user, sec_to_time(sum(tempo)) AS tempoSem1 FROM
  65                                                         (
  66                                                                 SELECT
  67                                                                         id_user, time_to_sec(timediff(fim, inicio)) AS tempo
  68                                                                 FROM
  69                                                                         historico JOIN config
  70                                                         WHERE ( ( DATE(historico.inicio) >= DATE(config.$campo_sql + INTERVAL 0 WEEK) ) AND ( DATE(historico.inicio) < DATE(config.$campo_sql + INTERVAL 1 WEEK) ) )
  71 ) AS sem1
  72                                                         GROUP BY id_user) AS sem1
  73                                                 USING (id_user)
  74                                                 LEFT JOIN
  75                                                         (SELECT id_user, sec_to_time(sum(tempo)) AS tempoSem2 FROM
  76                                                         (
  77                                                                 SELECT
  78                                                                         id_user, time_to_sec(timediff(fim, inicio)) AS tempo
  79                                                                 FROM
  80                                                                         historico JOIN config
  81                                                                 WHERE ( ( DATE(historico.inicio) >= DATE(config.$campo_sql + INTERVAL 1 WEEK) ) AND ( DATE(historico.inicio) < DATE(config.$campo_sql + INTERVAL 2 WEEK) ) )
  82                                                         ) AS sem2
  83                                                         GROUP BY id_user) AS sem2
  84                                                 USING (id_user)
  85                                                 LEFT JOIN
  86                                                         (SELECT id_user, sec_to_time(sum(tempo)) AS tempoSem3 FROM
  87                                                         (
  88                                                                 SELECT
  89                                                                         id_user, time_to_sec(timediff(fim, inicio)) AS tempo
  90                                                                 FROM
  91                                                                         historico JOIN config
  92                                                                 WHERE ( ( DATE(historico.inicio) >= DATE(config.$campo_sql + INTERVAL 2 WEEK) ) AND ( DATE(historico.inicio) < DATE(config.$campo_sql + INTERVAL 3 WEEK) ) )
  93                                                         ) AS sem3
  94                                                         GROUP BY id_user) AS sem3
  95                                                 USING (id_user)
  96                                                 LEFT JOIN
  97                                                         (SELECT id_user, sec_to_time(sum(tempo)) AS tempoSem4 FROM
  98                                                         (
  99                                                                 SELECT
 100                                                                         id_user, time_to_sec(timediff(fim, inicio)) AS tempo
 101                                                                 FROM
 102                                                                         historico JOIN config
 103                                                                 WHERE ( ( DATE(historico.inicio) >= DATE(config.$campo_sql + INTERVAL 3 WEEK) ) AND ( DATE(historico.inicio) < DATE(config.$campo_sql + INTERVAL 4 WEEK) ) )
 104                                                         ) AS sem4
 105                                                         GROUP BY id_user) AS sem4
 106                                                 USING (id_user)
 107                                                 )
 108                                 ORDER BY login";
 109           $pesq_horas = mysql_query($sql);
 110
 111         if(mysql_num_rows($pesq_horas) == 0)
 112         {
 113                 echo "<center style='color: #ff0000;'>Não há dados para este período!!!</center>";
 114                 echo "<table align='center'>$mes_anterior $prox_mes</table>";
 115         }
 116         else
 117         {
 118                 echo "<div style='width: 900px;margin:0 auto;'>\n";
 119                 echo "<table border=0 class='bordasimples' align='center'>\n";
 120                 echo "<tr>\n";
 121                 echo $mes_anterior;
 122                 echo "<td>\n";
 123                 echo "<table border=2 class='bordasimples' align='center'>\n";
 124                 echo "<thead>\n";
 125                 echo "<th>Nome</th>\n";
 126
 127                   $num_semana = mysql_result($rs, 0, 'num_semana');
 128                   for($i=1;$i<5;$i++)
 129                   {
 130                         if($num_semana == $i)
 131                                 $class = "style='background-color:#000099'";
 132                         else
 133                                 $class = "";
 134                         echo "<th ". $class .">Sem. ". $i ."<br>";
 135                         echo "(". mysql_result($rs, 0, 'iniSem'.$i) ." - ". mysql_result($rs, 0, 'fimSem'.$i) .")";
 136                   }
 137                 echo "<th>&nbsp;&nbsp;&nbsp;&nbsp;Total&nbsp;&nbsp;&nbsp;&nbsp;</th>";
 138                 echo "</thead>\n";
 139                 while($usuario = mysql_fetch_array($pesq_horas))
 140                 {
 141                         echo "<tr class='tbrow'>\n";
 142                         echo "<td>". $usuario['login'] ."</td>\n";
 143                         for($i=1;$i<5;$i++)
 144                                 echo "<td>". $usuario['tempoSem'.$i] ."</td>";
 145
 146                         $total = calcTotal($usuario);
 147
 148                         echo "<td>".$total."</td>";
 149
 150                         echo "</tr>\n";
 151                 }
 152                 echo "</table>\n";
 153                 echo $prox_mes;
 154                 echo "</tr>\n";
 155                 echo "</table>\n";
 156                 echo "</div>";
 157         }
 158
 159         function calcTotal($usuario) {
 160
 161                 $total = 0;
 162                 for($i=1;$i<5;$i++) {
 163
 164                         $total+=getMinutes($usuario['tempoSem'.$i]);
 165                 }
 166                 $horas = (int) ($total/60);
 167                 $minutos = $total%60;
 168                 if($horas < 48)
 169                         $res = "<font color=#FF0000>".$horas."h ".$minutos."min</font>";
 170                 else
 171                         $res = $horas."h ".$minutos."min";
 172                 return $res;
 173         }
 174
 175         function getMinutes($stringHoras) {
 176
 177                 $horas = 0;
 178                 $minutos = 0;
 179                 $n = 0;
 180                 $res = 0;
 181
 182                 $n = sscanf($stringHoras, "%dh %dmin", $horas, $minutos);
 183
 184                 if($n == 2) {
 185
 186                         $res+=$horas*60 + $minutos;
 187                 }
 188                 return $res;
 189         }
 190
 191         ?>
 192
 193         <br>
 194
 195         <div style="clear:both"></div>
 196         <center>
 197                         <a href="admin.php"><< Administrador</a><br>
 198                         <a href="index.php"><< Logador</a>
 199         </center>
 200 </body>
 201 </html>