select

[Lucas Fernando 0]

Ola pessoal boa tarde tudo bem ?

 

Estou com problemas para montar uma consulta SQL na verdade não sei se o problema esta na consulta ou no php.

 

mais estou postando aqui pois eu creio que exista uma forma melhor de fazer.

 

Na verdade eu preciso de um select que me traga informações na vertical e horizontal.

 

seria basicamente isso :

 

PROJETO

nome proj1 proj2 proj3 proj4 proj5 proj6 proj7 proj8 proj9 proj10 proj11 proj12

fulano 2,0 5,0 2,0 4,0

ciclano 1,0

beltrano 3,0 3,0 2,0 2,0

 

 

Para isso tenho três tabelas:

 

TIME_TYPES --- PROJETOS

CREATE TABLE IF NOT EXISTS `time_types` (
`type_id` varchar(10) NOT NULL,
`description` varchar(100) NOT NULL DEFAULT '',
`status` char(1) NOT NULL,
PRIMARY KEY (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

 

TIME_DATA ---- HORAS TRABALHADAS

CREATE TABLE IF NOT EXISTS `time_data` (
`time_id` int(10) NOT NULL AUTO_INCREMENT,
`user_id` int(10) NOT NULL DEFAULT '0',
`function_fk` int(10) NOT NULL,
`data_date` date DEFAULT NULL,
`type_id` varchar(10) NOT NULL DEFAULT '0',
`work_id_fk` int(10) NOT NULL,
`hours` double DEFAULT NULL,
`notes` text,
`user_appro_fk` int(10) NOT NULL,
`status_appro` char(1) NOT NULL,
PRIMARY KEY (`time_id`),
KEY `user_id` (`user_id`),
KEY `type_id` (`type_id`),
KEY `work_id_fk` (`work_id_fk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=407 ;

 

 

USER_INFO --- USUARIO/FUNCIONARIO

CREATE TABLE IF NOT EXISTS `user_info` (
`user_id` int(10) NOT NULL AUTO_INCREMENT,
`fname` varchar(50) DEFAULT NULL,
`lname` varchar(50) DEFAULT NULL,
`dpt_id_fk` int(10) NOT NULL,
`function_id_fk` int(10) NOT NULL DEFAULT '0',
`level` varchar(20) NOT NULL DEFAULT 'User',
`username` varchar(30) NOT NULL DEFAULT '',
`password` varchar(40) NOT NULL DEFAULT '',
PRIMARY KEY (`user_id`),
UNIQUE KEY `username` (`username`),
KEY `function_id_fk` (`function_id_fk`),
KEY `function_id_fk_2` (`function_id_fk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ;

 

 

Não sei se consigo criar algum select parecido com o exemplo acima com algum comando especifico, ja pesquisei bastante.

[Motta 645]

Use o CASE para fazer este PIVOT, exemplos aos montes até aqui no Imsters mesmo.

http://forum.imasters.com.br/topic/515040-listar-quantidade-de-pedidos-por-ms-sem-repetir-cliente/

[Lucas Fernando 0]

--
-- Estrutura da tabela `time_data` - Horas apontadas
--

CREATE TABLE IF NOT EXISTS `time_data` (
  `time_id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) NOT NULL DEFAULT '0',
  `function_fk` int(10) NOT NULL,
  `data_date` date DEFAULT NULL,
  `type_id` int(10) NOT NULL DEFAULT '0',
  `work_id_fk` int(10) NOT NULL,
  `hours` double DEFAULT NULL,
  `notes` text,
  `user_appro_fk` int(10) DEFAULT NULL,
  `status_appro` char(1) NOT NULL,
  PRIMARY KEY (`time_id`),
  KEY `user_id` (`user_id`),
  KEY `type_id` (`type_id`),
  KEY `work_id_fk` (`work_id_fk`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;


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

--
-- Estrutura da tabela `time_types` - Projetos
--

CREATE TABLE IF NOT EXISTS `time_types` (
  `type_id` int(10) NOT NULL AUTO_INCREMENT,
  `description` varchar(100) NOT NULL DEFAULT '',
  `status` char(1) NOT NULL,
  PRIMARY KEY (`type_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=100001 ;

--


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

--
-- Estrutura da tabela `time_work` - Tipo de hora
--

CREATE TABLE IF NOT EXISTS `time_work` (
  `work_id` int(10) NOT NULL AUTO_INCREMENT,
  `work_name` char(2) NOT NULL DEFAULT '',
  `work_desc` varchar(15) NOT NULL DEFAULT '',
  PRIMARY KEY (`work_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;




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

--
-- Estrutura da tabela `user_department` - Departamento
--

CREATE TABLE IF NOT EXISTS `user_department` (
  `dpt_id` int(10) NOT NULL AUTO_INCREMENT,
  `dpt_descri` varchar(100) NOT NULL,
  `dpt_status` char(1) NOT NULL,
  PRIMARY KEY (`dpt_id`),
  UNIQUE KEY `dpt_descri` (`dpt_descri`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;



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

--
-- Estrutura da tabela `user_functions` - Funções
--

CREATE TABLE IF NOT EXISTS `user_functions` (
  `function_id` int(10) NOT NULL AUTO_INCREMENT,
  `function_desc` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`function_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;



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

--
-- Estrutura da tabela `user_info` - Usuários
--

CREATE TABLE IF NOT EXISTS `user_info` (
  `user_id` int(10) NOT NULL AUTO_INCREMENT,
  `fname` varchar(50) DEFAULT NULL,
  `lname` varchar(50) DEFAULT NULL,
  `function_id_fk` int(10) NOT NULL DEFAULT '0',
  `dpt_id_fk` int(10) NOT NULL,
  `level` varchar(20) NOT NULL DEFAULT 'User',
  `username` varchar(30) NOT NULL DEFAULT '',
  `password` varchar(40) NOT NULL DEFAULT '',
  PRIMARY KEY (`user_id`),
  KEY `function_id_fk` (`function_id_fk`),
  KEY `function_id_fk_2` (`function_id_fk`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;


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

--
-- Estrutura da tabela `user_log`
--

CREATE TABLE IF NOT EXISTS `user_log` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) NOT NULL,
  `loginip` varchar(25) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `lastlogin` date NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--

Ai está minha database

[Motta 645]

De onde vem as informacoes dos projetos ?

 

O que se soma ?

[Lucas Fernando 0]

então eu fiz um select muito doido mais tras as informações de forma diferente, onde cada linha traz o nome do funcionario e soma por projeto usei um group by e o sum para soman as horas.

 

Porem como te disse acima o que o pessoal quer é um modelo diferente de relatorio com uma visão panoramica.

 

a tabela responsavel pelas horas é a

time_data

as horas vem do campo hours.

 

 

No caso o usuario entra em uma tela onde digita a data, projeto, tipo de hora (normal ou extra), quantidade de horas (campo hours) e por ultimo o campo notes que não é obrigatorio.

[Motta 645]

Use o CASE para fazer o que chama de panoramica (pivot).

 

Alguns programas de relatórios como o Crystal Reports fazem isto, o Excel também, mas dá para fazer vua SQL.

[Lucas Fernando 0]

Ainda estou quebrando a cabeça aqui,

 

Não estou imaginando como usar o case.

 

Afinal existiram projetos novos todo o momento.

 

Não conhecia o privot.

 

Vou continuar tentando aqui.

[Motta 645]

O ruim do CASE é este novos projetos "não pega"

Uma solução é usar ELSE e lançar em "OUTROS" , não é lá uma grande solução.

 

O ideal é usar um software que já faça o PIVOT pois novos projetos aparecem de forma automática.

[Lucas Fernando 0]

Então pior é que isso vai aparecer na web estou usando php.

[Motta 645]

Tentou algo assim ?

[Lucas Fernando 0]

Motta, não sei se o ideal seria eu abrir um novo topico na area de php mais de uma olhada no que eu estava pensando em fazer.

 

$busca_pro = mysql_query("select pro.type_id, pro.description,
					       dat.type_id, dat.data_date
						   From time_types pro, time_data dat
						   Where dat.type_id = pro.type_id and 
						   dat.data_date between '$start_date' and '$end_date'
						   Group by pro.type_id
						   order by pro.type_id")or die(mysql_error());//faz a busca com os projetos filtrados no periodo
						   
		
		$result_pro = mysql_query ("select dat.user_id, dat.type_id, pro.type_id,
                           pro.description,	usr.user_id, usr.fname,				
       " while ($result = mysql_fetch_array($busca_pro)) { "
	   sum(case when pro.type_id = '$result[type_id]' then 1 else 0 end) '$result[description]', 
	   " } "
	   sum(case when pro.type_id = '000010' then 1 else 0 end) 'UTC BASUL CARREG'
       from time_data dat, time_types pro, user_info usr
       where dat.type_id = pro.type_id
       AND usr.user_id = dat.user_id AND
	   dat.data_date between '$start_date' and '$end_date'
       group by dat.user_id")or die(mysql_error());//faz a busca com os projetos do periodo

Só não sei se é possível colocar um while assim no meio de um mysql_query, se você tiver alguma ideia tambem agradeço.

[Motta 645]

Melhor abrir em php, só fiz fazer uma busco no Google , não sei avaliar aquele código