Ir para conteúdo
Fórum Script Brasil
  • 0

Montar tabela crosstab

RRH

Perguntado por RRH,

 Share

Pergunta

RRH

RRH

Postado
Postado

Tenho a seguinte tabela no MySQL:

-- phpMyAdmin SQL Dump
-- version 3.3.9
-- http://www.phpmyadmin.net
--
-- Servidor: localhost
-- Tempo de Geração: Jul 09, 2013 as 12:48 AM
-- Versão do Servidor: 5.5.8
-- Versão do PHP: 5.3.5

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Banco de Dados: `base`
--

-- --------------------------------------------------------

--
-- Estrutura da tabela `horas`
--

DROP TABLE IF EXISTS `horas`;
CREATE TABLE IF NOT EXISTS `horas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nrfunc` int(7) NOT NULL,
  `data` date NOT NULL,
  `diames` int(2) NOT NULL,
  `semana` int(2) NOT NULL,
  `hi` datetime NOT NULL,
  `ht` datetime NOT NULL,
  `total` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=35 ;

--
-- Extraindo dados da tabela `horas`
--

INSERT INTO `horas` (`id`, `nrfunc`, `data`, `diames`, `semana`, `hi`, `ht`, `total`) VALUES
(1, 9288, '2013-06-01', 1, 22, '2013-06-01 08:30:00', '2013-06-01 13:00:00', '4.5'),
(2, 9288, '2013-06-02', 2, 23, '2013-06-02 08:30:00', '2013-06-02 18:00:00', '9.5'),
(3, 9288, '2013-06-19', 19, 25, '2013-06-19 08:30:00', '2013-06-19 18:00:00', '9.5'),
(4, 9288, '2013-06-04', 4, 23, '2013-06-04 08:30:00', '2013-06-04 18:00:00', '9.5'),
(5, 9288, '2013-06-05', 5, 23, '2013-06-05 08:30:00', '2013-06-05 18:00:00', '9.5'),
(6, 9288, '2013-06-06', 6, 23, '2013-06-06 08:30:00', '2013-06-06 18:00:00', '9.5'),
(7, 9288, '2013-06-07', 7, 23, '2013-06-07 08:30:00', '2013-06-07 18:00:00', '9.5'),
(8, 9288, '2013-06-08', 8, 23, '2013-06-08 08:30:00', '2013-06-08 18:00:00', '9.5'),
(9, 9288, '2013-06-09', 9, 24, '2013-06-09 08:30:00', '2013-06-09 18:00:00', '9.5'),
(10, 9288, '2013-06-10', 10, 24, '2013-06-10 08:30:00', '2013-06-10 18:00:00', '9.5'),
(11, 9288, '2013-06-11', 11, 24, '2013-06-11 08:30:00', '2013-06-11 18:00:00', '9.5'),
(12, 9288, '2013-06-15', 15, 24, '2013-06-15 08:30:00', '2013-06-15 18:00:00', '9.5'),
(13, 9288, '2013-06-16', 16, 25, '2013-06-16 14:00:00', '2013-06-16 20:00:00', '6'),
(14, 9288, '2013-06-16', 16, 25, '2013-06-16 14:00:00', '2013-06-16 20:00:00', '6'),
(15, 9288, '2013-06-18', 18, 25, '2013-06-18 14:00:00', '2013-06-18 20:00:00', '6'),
(16, 9288, '2013-06-19', 19, 25, '2013-06-19 14:00:00', '2013-06-19 20:00:00', '6'),
(17, 9288, '2013-06-20', 20, 25, '2013-06-20 14:00:00', '2013-06-20 20:00:00', '6'),
(18, 9288, '2013-06-22', 22, 25, '2013-06-22 14:00:00', '2013-06-22 20:00:00', '6'),
(19, 9288, '2013-06-23', 23, 25, '2013-06-23 14:00:00', '2013-06-23 20:00:00', '6'),
(20, 9288, '2013-06-24', 24, 25, '2013-06-24 14:00:00', '2013-06-24 20:00:00', '6'),
(21, 1955, '2013-06-02', 2, 23, '2013-06-02 07:00:00', '2013-06-02 19:00:00', '12'),
(22, 1955, '2013-06-03', 3, 23, '2013-06-03 19:00:00', '2013-06-04 07:00:00', '12'),
(23, 1955, '2013-06-06', 6, 23, '2013-06-06 07:00:00', '2013-06-06 19:00:00', '12'),
(24, 1955, '2013-06-07', 7, 23, '2013-06-07 19:00:00', '2013-06-08 07:00:00', '12'),
(25, 1955, '2013-06-10', 10, 24, '2013-06-10 07:00:00', '2013-06-10 19:00:00', '12'),
(26, 1955, '2013-06-11', 11, 24, '2013-06-11 19:00:00', '2013-06-12 07:00:00', '12'),
(27, 1955, '2013-06-14', 14, 24, '2013-06-14 07:00:00', '2013-06-14 19:00:00', '12'),
(28, 1955, '2013-06-15', 15, 24, '2013-06-15 19:00:00', '2013-06-16 07:00:00', '12'),
(29, 1955, '2013-06-18', 18, 25, '2013-06-18 07:00:00', '2013-06-18 19:00:00', '12'),
(30, 1955, '2013-06-19', 19, 25, '2013-06-19 19:00:00', '2013-06-20 07:00:00', '12'),
(33, 5181, '2013-06-07', 7, 23, '2013-06-07 08:30:00', '2013-06-07 18:00:00', '9.5'),
(34, 9288, '2013-06-25', 25, 25, '2013-06-25 14:00:00', '2013-06-25 20:00:00', '6');

É possível montar um relatório igual ao da imagem abaixo:

n0gc.png

Eu consegui chegar até aqui com ajuda em outro fórum:

<?php
include "config.php";

$sql = "SELECT
nrfunc AS FUNCIONARIO,
SUM(IF(diames = '1',total,0)) AS '1',
SUM(IF(diames = '2',total,0)) AS '2',
SUM(IF(diames = '3',total,0)) AS '3',
SUM(IF(diames = '4',total,0)) AS '4',
SUM(IF(diames = '5',total,0)) AS '5',
SUM(IF(diames = '6',total,0)) AS '6',
SUM(IF(diames = '7',total,0)) AS '7',
SUM(IF(diames = '8',total,0)) AS '8',
SUM(IF(diames = '9',total,0)) AS '9',
SUM(IF(diames = '10',total,0)) AS '10',
SUM(IF(diames = '11',total,0)) AS '11',
SUM(IF(diames = '12',total,0)) AS '12',
SUM(IF(diames = '13',total,0)) AS '13',
SUM(IF(diames = '14',total,0)) AS '14',
SUM(IF(diames = '15',total,0)) AS '15',
SUM(IF(diames = '16',total,0)) AS '16',
SUM(IF(diames = '17',total,0)) AS '17',
SUM(IF(diames = '18',total,0)) AS '18',
SUM(IF(diames = '19',total,0)) AS '19',
SUM(IF(diames = '20',total,0)) AS '20',
SUM(IF(diames = '21',total,0)) AS '21',
SUM(IF(diames = '22',total,0)) AS '22',
SUM(IF(diames = '23',total,0)) AS '23',
SUM(IF(diames = '24',total,0)) AS '24',
SUM(IF(diames = '25',total,0)) AS '25',
SUM(IF(diames = '26',total,0)) AS '26',
SUM(IF(diames = '27',total,0)) AS '27',
SUM(IF(diames = '28',total,0)) AS '28',
SUM(IF(diames = '29',total,0)) AS '29',
SUM(IF(diames = '30',total,0)) AS '30',
SUM(IF(diames = '31',total,0)) AS '31',
SUM(COALESCE(total,0)) as 'TOTAL'
FROM
horas
GROUP BY 1
WITH ROLLUP";

$rs = mysql_query($sql) OR die(mysql_error());

// montando o header da tabela
$header = array();
for($i=0, $total=mysql_num_fields($rs); $i<$total; $i++){
$header[] = mysql_field_name($rs, $i);
}

$header_str = '<tr><th>' . implode('</th><th>', $header) . '</th></tr>';


// montando as demais linhas da tabela
$html = '';
while($row = mysql_fetch_assoc($rs)){
$html .= "<tr>";
foreach($row as $key => $value){
if($key == 'FUNCIONARIO' && is_null($value)){
$value = 'TOTAL GERAL';
}
if($key != 'FUNCIONARIO'){
$html .= "<td align='center'>" . $value . "</td>";
}else{
$html .= "<td>" . $value . "</td>";
}
}
$html .= "</tr>";
}


// exibindo a tabela final
echo "<div id='regula'><table class='tblGrid'>", $header_str, $html, "</table></div>";

?>
Link para o comentário
Compartilhar em outros sites

2 respostass a esta questão

Posts Recomendados

  • 0
Markus Magnus

Markus Magnus

Postado
Postado

Esse foi a postagem mais trabalhosa até hoje, levei umas 2 horas para terminar, mas ta praticamente pronta, ao meu ver, enfim...

Como não manjo muito de MySQL e como você separar as semanas, somar e subtrair valores, fiz todo esse trampo no PHP mesmo.

<style type="text/css">
.ts {
	background:#2a5;
}
.th {
	background:#888;
}
.ne {
	color:#900;
}
.po {
	color:#009;
}
</style>
<?php
$nome_banco = 'teste';
$senha_banco = 'kill90';
$usuario_banco = 'root';
$local_banco = 'localhost';
$conexao = mysql_pconnect($local_banco, $usuario_banco, $senha_banco) or die('Não foi possível conectar: '.mysql_error());
$banco = mysql_select_db ($nome_banco, $conexao) or die('Não foi possível conectar no banco: '.mysql_error());
//selecionar anos
$sql = "SELECT DISTINCT YEAR(data) as ano FROM horas";
$anos = mysql_query($sql) OR die(mysql_error());
//loop em anos
while ($ano = mysql_fetch_assoc($anos)) {
//	selecionar meses
	$sql = "SELECT DISTINCT MONTH(data) as mes FROM horas WHERE YEAR(data) = {$ano['ano']}";
	$meses = mysql_query($sql) OR die(mysql_error());
//	loop em meses
	while ($mes = mysql_fetch_assoc($meses)) {
		$html .= "<table border=\"1\">";
		$html .= "\n<thead>";
		$html .= "\n<th class=\"th\">";
		$html .= "Funcionário";
		$html .= "</th>";
//		loop de 1 até 31
		for ($i=1;$i<32;$i++) {
//			se o ano, mes e dia corrente existem
			if (checkdate($mes['mes'], $i, $ano['ano'])) {
//				se a semana do dia corrente for diferente a semana do dia anterior
				if (date('W', mktime(0,0,0,$mes['mes'], $i, $ano['ano'])) != date('W', mktime(0,0,0,$mes['mes'],$i-1,$ano['ano']))) {
					$html .= "\n<th class=\"ts\">";
					$html .= "T";
					$html .= "</th>";
					$html .= "\n<th class=\"ts\">";
					$html .= "S";
					$html .= "</th>";
				}
//				escreve o dia da coluna
				$html .= "\n<th class=\"th\">";
				$html .= $i;
				$html .= "</th>";
			}
		}
		$html .= "\n</thead>";
		$html .= "\n<tbody>";
//		selecionar funcionários que trabalharam no mês corrente
		$sql = "SELECT DISTINCT nrfunc FROM horas WHERE DATE_FORMAT(data, '%Y-%c') = '{$ano['ano']}-{$mes['mes']}' ORDER BY nrfunc";
		$funcionarios = mysql_query($sql) OR die(mysql_error());
//		loop em funcionários
		while ($f = mysql_fetch_assoc($funcionarios)) {
			$html .= "\n<tr>";
//			escreve numero do funcionario
			$html .= "\n<td class=\"th\">";
			$html .= $f['nrfunc'];
			$html .= "</td>";
//			selecionar semanas do mês corrente
			$sql = "SELECT DISTINCT semana FROM horas WHERE DATE_FORMAT(data, '%Y-%c') = '{$ano['ano']}-{$mes['mes']}' ORDER BY data";
			$semanas = mysql_query($sql) OR die(mysql_error());
//			loop em semanas
			while ($s = mysql_fetch_assoc($semanas)) {
//				loop de 1 até 31
				for ($i=1;$i<32;$i++) {
//					se o ano, mes e dia corrente existem
					if (checkdate(date('m'), $i, date('Y'))) {
//						se a semana do dia corrente for igual a semana do loop de semanas
						if (date('W', mktime(0,0,0,$mes['mes'], $i, date('Y'))) == $s['semana']) {
//							seleciona horas trabalhadas no dia corrente do funcionário corrente
							$sql = "SELECT total FROM horas WHERE DATE_FORMAT(data, '%Y-%c-%e') = '{$ano['ano']}-{$mes['mes']}-$i' and nrfunc = {$f['nrfunc']}";
//							echo "SELECT total FROM horas WHERE DATE_FORMAT(data, '%Y-%c-%e') = '{$ano['ano']}-{$mes['mes']}-$i' and nrfunc = {$f['nrfunc']}\n";
							$horas = mysql_query($sql) OR die(mysql_error());
//							escreve horas trabalhadas
							$html .= "\n<td>";
							if (mysql_num_rows($horas) > 0) {
								$hora = mysql_fetch_assoc($horas);
								$html .= $hora['total'];
//								soma as horas da semana
								$soma += $hora['total'];
							} else {
								$html .= "0";
							}
							$html .= "</td>";
						}
					}
				}
//				escreve soma de horas
				$html .= "\n<td class=\"ts\">";
				$html .= $soma;
				$html .= "</td>";
//				escreve o saldo
				$c = 'po';
				$soma = $soma-40;
				if ($soma < 0) {
					$soma = $soma*-1;
					$c = 'ne';
				}
				$html .= "\n<td class=\"ts $c\">";
				$html .= $soma;
				$soma = 0;
				$html .= "</td>";
			}
			$html .= "\n</tr>";
		}
		$html .= "\n</tbody>";
		$html .= "\n</table>";
	}
}
echo $html;
?>

Espero ter ajudado

Abraços

Link para o comentário
Compartilhar em outros sites
  • 0
RRH

RRH

Postado
  • Autor
Postado (editado)

Não sei o que aconteceu, mas não recebi notificação no meu email de sua resposta caro Markus Magnus. Resolvi dar uma olhada nos meus post e foi aí que ví a sua resposta. Muito obrigado pelo sua ajuda. Testei seu código aqui, mas ele não está fazendo todos os cálculos, veja as colunas T e S:

a912.png

Outra coisa é a separação de semana por cada 7 dias, ou seja, depois dos dias 7, 14, 21, 28 e 30 ou 31 aí teria as colunas T e S.

Já que você disse:

...mas ta praticamente pronta, ao meu ver, enfim...

eu pensei em abusar de sua ajuda mais um pouco para revisar o código com as considerações que fiz. Ficarei imensamente grato.

Editado por RRH
Link para o comentário
Compartilhar em outros sites

Participe da discussão

Você pode postar agora e se registrar depois. Se você já tem uma conta, acesse agora para postar com sua conta.

Visitante
Responder esta pergunta...

×   Você colou conteúdo com formatação.   Remover formatação

  Apenas 75 emoticons são permitidos.

×   Seu link foi incorporado automaticamente.   Exibir como um link em vez disso

×   Seu conteúdo anterior foi restaurado.   Limpar Editor

×   Você não pode colar imagens diretamente. Carregar ou inserir imagens do URL.

×
 Share
Ir para lista de Perguntas


  • Estatísticas dos Fóruns

    • Tópicos
      152,3k
    • Posts
      652,3k
×
×
  • Criar Novo...