00) Mostra o TOTAL de pedidos dos DVDs seperados por um intervalo de Datas

SELECT ticket_dept_id AS PediDVD, user_id, title, opened AS Data
FROM pgnzt_fss_ticket_ticket
WHERE ticket_dept_id=2
AND opened BETWEEN '2015-01-01' AND '2016-12-30'
ORDER BY opened ASC;

Obs: COUNT(*)

----------------------------------------------------------------------------------
0) Mostra os pedidos de DVDs REPETIDOS

SELECT pgnzt_fss_ticket_ticket.ticket_dept_id AS PediDVD, pgnzt_fss_ticket_ticket.user_id, pgnzt_users.name AS Nome, COUNT(*) AS Repeticao
FROM pgnzt_fss_ticket_ticket, pgnzt_users
WHERE ticket_dept_id=2
AND pgnzt_users.id = pgnzt_fss_ticket_ticket.user_id
GROUP BY user_id
HAVING COUNT(*) > 1;

-----------------------------------------------------------------------------------
000) Mostra o total de pedidos DVDs separado por Estados

SELECT pgnzt_comprofiler.cb_estado AS Estado, COUNT(*) AS Pedidos
FROM pgnzt_comprofiler, pgnzt_fss_ticket_ticket
WHERE pgnzt_fss_ticket_ticket.ticket_dept_id =2
AND pgnzt_comprofiler.user_id = pgnzt_fss_ticket_ticket.user_id
GROUP BY pgnzt_comprofiler.cb_estado
ORDER BY `Pedidos` DESC

-----------------------------------------------------------------------------------
001) Mostra o total de certificados emitidos em um determinado periodo

SELECT certificateid AS IdCurso, userid, DAY( from_unixtime( timecreated ) ) AS Dia, MONTH( from_unixtime( timecreated ) ) AS Mes, YEAR( from_unixtime( timecreated ) ) AS Ano, code
FROM certificate_issues
WHERE timecreated
BETWEEN '1420070399'
AND '1451606399'
ORDER BY id ASC


OBS: os intervalos de datas devem ser em formato TimeStamp
-----------------------------------------------------------------------------------

OBS: Essas consultas são para a nova base de dados do moodle 2.6 e joomla, ano de 2015

1)Pegando os dados do Moodle e do Joomla, todos com CERTIFICADOS emitidos por cursos por ano e mês

SELECT grade_grades.userid AS IdAluno, course.fullname AS Curso, pgnzt_comprofiler.cb_escolaridade, pgnzt_comprofiler.cb_tipodecadastro, pgnzt_comprofiler.cb_estado, user.email, grade_grades.finalgrade, MONTH(from_unixtime(certificate_issues.timecreated)) AS Mes, YEAR(from_unixtime(certificate_issues.timecreated)) AS Ano
FROM certificate_issues, user, grade_grades, pgnzt_comprofiler, pgnzt_users, course, certificate
WHERE user.id = certificate_issues.userid
AND itemid = 45 /* Id da avaliação do curso na tabela grade_items */
AND certificateid = 7/* Id do curso na tabela certificate_issues */
AND grade_grades.userid = certificate_issues.userid
AND pgnzt_users.email = user.email
AND pgnzt_comprofiler.id = pgnzt_users.id
AND certificate.course = course.id
AND certificate.id = certificate_issues.certificateid
ORDER BY certificate_issues.timecreated ASC


Obs: Pegar itemid e certificateid para a consulta

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

2) Pegar itemId do cursos na tabela grade_itens

SELECT DAY(from_unixtime(grade_items.timecreated)) AS Dia, MONTH(from_unixtime(grade_items.timecreated)) AS Mes, YEAR(from_unixtime(grade_items.timecreated)) AS Ano, grade_items.id AS ItemId, grade_items.courseid AS IdCurso, course.fullname As Curso, grade_items.itemname FROM grade_items, course
WHERE grade_items.courseid = course.id
ORDER BY grade_items.timecreated DESC

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

3) Apresenta os cursos que o aluno possuí CERTIFICADOS e suas respectivas DATAS

SELECT certificate_issues.userid AS IdUsuario, user.firstname AS Nome, user.lastname As Sobrenome, course.fullname AS Curso, DAY(from_unixtime(certificate_issues.timecreated)) AS Dia, MONTH(from_unixtime(certificate_issues.timecreated)) AS Mes, YEAR( from_unixtime(certificate_issues.timecreated)) AS Ano, certificate_issues.code
FROM certificate_issues, course, certificate, user
WHERE certificate_issues.userid=???
AND certificate.course = course.id
AND certificate.id = certificate_issues.certificateid
AND user.id = certificate_issues.userid
ORDER BY certificate_issues.timecreated DESC

Obs: Mudar apenas o ID do USUÁRIO certificate_issues.userid=????

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

5) Mostra as notas do aluno tirada nos cursos = TABELA quiz_attempts

SELECT quiz_attempts.userid AS Id, user.firstname AS Nome, user.lastname As Sobrenome, course.fullname AS Curso, quiz_attempts.sumgrades AS Notas, DAY(from_unixtime(quiz_attempts.timestart)) AS Dia, MONTH(from_unixtime(quiz_attempts.timestart)) AS Mes, YEAR(from_unixtime(quiz_attempts.timestart)) AS Ano
FROM quiz, quiz_attempts, course, user
WHERE quiz_attempts.userid = ???
AND quiz_attempts.sumgrades >= 7.00000
AND quiz.id = quiz_attempts.quiz
AND quiz.course = course.id
AND user.id = quiz_attempts.userid

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

6) Mostra o total de certificado emitidos por estado e cidade

SELECT pgnzt_users.name AS Nome, pgnzt_users.email AS Email, pgnzt_comprofiler.cb_estado AS Estado, pgnzt_comprofiler.cb_municipio AS Cidade, course.fullname AS Curso, COUNT( certificate_issues.id ) Total
FROM pgnzt_comprofiler, certificate_issues, pgnzt_users, user, course, certificate
WHERE pgnzt_comprofiler.user_id = pgnzt_users.id
AND pgnzt_comprofiler.cb_estado = 'pb'
AND pgnzt_users.email = user.email
AND user.id = certificate_issues.userid
AND certificate.course = course.id
AND certificate.id = certificate_issues.certificateid
GROUP BY pgnzt_comprofiler.cb_municipio, certificate_issues.certificateid

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

7) Mostra o total de cadastrados por estado e cidade

SELECT pgnzt_users.name AS Nome, pgnzt_users.email AS Email, pgnzt_comprofiler.cb_estado AS Estado, pgnzt_comprofiler.cb_municipio AS Cidade, COUNT( pgnzt_comprofiler.user_id ) Total
FROM pgnzt_comprofiler, pgnzt_users
WHERE pgnzt_comprofiler.user_id = pgnzt_users.id
AND pgnzt_comprofiler.cb_estado = 'pb'
GROUP BY pgnzt_comprofiler.cb_municipio


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

8) Mostra o total de certificados emitidos em todos os cursos! (Solicitação Mariana de Brasília)

SELECT pgnzt_comprofiler.cb_estado AS Estado, pgnzt_comprofiler.cb_municipio AS Cidade, course.fullname AS Curso, COUNT( certificate_issues.id ) Total
FROM pgnzt_comprofiler, certificate_issues, pgnzt_users, user, course, certificate
WHERE pgnzt_comprofiler.user_id = pgnzt_users.id
AND pgnzt_users.email = user.email
AND user.id = certificate_issues.userid
AND certificate.course = course.id
AND certificate.id = certificate_issues.certificateid
AND certificate_issues.timecreated BETWEEN '1420070399' AND '1451606399'
GROUP BY pgnzt_comprofiler.cb_municipio, course.fullname, pgnzt_comprofiler.cb_estado
ORDER BY `Cidade` ASC

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

9) Mostra o total de certificados emitidos de TODOS OS CURSOS dentro de uma Grid

SELECT YEAR( from_unixtime(certificate_issues.timecreated)) AS Ano, course.fullname AS Curso, COUNT( certificate_issues.certificateid ) AS Total
FROM certificate_issues, course, certificate
WHERE certificate.course = course.id
AND certificate.id = certificate_issues.certificateid
AND certificate_issues.timecreated BETWEEN '1451606400' AND '1459382400'
GROUP BY course.fullname
ORDER BY `Total` DESC

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

10) Apresenta o total de cadastrados por semestre

SELECT COUNT( id ) AS Total
FROM pgnzt_users
WHERE registerDate BETWEEN '2013-01-01 00:00:00' AND '2013-06-30 00:00:00'

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

11) Apresenta o total de certificados emitidos de TODOS os cursos - Separados por TODOS os ESTADOS

SELECT pgnzt_comprofiler.cb_estado AS Estado, course.fullname AS Curso, COUNT( certificate_issues.certificateid ) AS Total, YEAR( from_unixtime( certificate_issues.timecreated ) ) AS Ano
FROM certificate_issues, course, certificate, pgnzt_comprofiler, pgnzt_users, user
WHERE certificate.course = course.id
AND certificate.id = certificate_issues.certificateid
AND certificate_issues.timecreated
AND pgnzt_users.email = user.email
AND pgnzt_comprofiler.id = pgnzt_users.id
AND user.id = certificate_issues.userid
AND certificate_issues.timecreated BETWEEN '1356998400' AND '1435708799'
GROUP BY course.fullname, pgnzt_comprofiler.cb_estado
ORDER BY course.fullname ASC

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

12) Mostra os cadastros REPETIDOS!

SELECT id, name As Nome, email, DATE_FORMAT( registerDate, '%d/%m/%Y' ) AS Data, COUNT( * ) AS Repeticao
FROM pgnzt_users
GROUP BY email
HAVING COUNT( * ) >1
ORDER BY registerDate DESC

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

13) Query que o Jose solicitou

SELECT YEAR( from_unixtime( certificate_issues.timecreated ) ) AS Ano, pgnzt_comprofiler.cb_estado AS Estado, pgnzt_comprofiler.cb_escolaridade AS Escolaridade, pgnzt_comprofiler.cb_tipodecadastro AS Tipo_de_cadastro, pgnzt_comprofiler.cb_areadeformacao AS Area_de_Formacao, pgnzt_comprofiler.cb_temp AS Profissao, COUNT( certificate_issues.certificateid ) AS Total_de_certificados
FROM certificate_issues, certificate, pgnzt_comprofiler, pgnzt_users, user, course
WHERE course.id =9
AND certificate.course = course.id
AND certificate.id = certificate_issues.certificateid
AND pgnzt_users.email = user.email
AND pgnzt_comprofiler.id = pgnzt_users.id
AND user.id = certificate_issues.userid
AND certificate_issues.timecreated BETWEEN '1356998400'AND '1388448000'
GROUP BY pgnzt_comprofiler.cb_estado, pgnzt_comprofiler.cb_escolaridade, pgnzt_comprofiler.cb_tipodecadastro, pgnzt_comprofiler.cb_areadeformacao, pgnzt_comprofiler.cb_temp
ORDER BY Estado ASC

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

14) Mostra os alunos que mais acessaram o site do TELELAB

SELECT u.id, u.firstname, u.lastname, u.email, COUNT( l.id ) AS Numero_de_visitas
FROM user u INNER JOIN log l ON u.id = l.userid
WHERE ACTION = 'login' GROUP BY u.id, u.firstname, u.lastname, u.email ORDER BY COUNT( l.id ) DESC


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

15) Mostra o total de certificados emitidos separado po estado

SELECT pgnzt_comprofiler.cb_estado AS Estado, course.fullname AS Curso, COUNT( certificate_issues.id ) Total
FROM pgnzt_comprofiler, certificate_issues, pgnzt_users, user, course, certificate
WHERE pgnzt_comprofiler.user_id = pgnzt_users.id
AND pgnzt_comprofiler.cb_estado = 'mg'
AND pgnzt_users.email = user.email
AND user.id = certificate_issues.userid
AND certificate.course = course.id
AND certificate.id = certificate_issues.certificateid
AND certificate_issues.timecreated BETWEEN '1451606340' AND '1479742380'
GROUP BY pgnzt_comprofiler.cb_estado, course.fullname, pgnzt_comprofiler.cb_estado ORDER BY `Estado` ASC

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

16) Mostra o total de certiifcados emitidos por ESCOLARIDADE e AREA DE FORMAÇÃO

SELECT YEAR( from_unixtime( certificate_issues.timecreated ) ) AS Ano, course.fullname AS Curso, pgnzt_comprofiler.cb_escolaridade AS Escolaridade, pgnzt_comprofiler.cb_areadeformacao AS Area_de_Formacao, COUNT( certificate_issues.certificateid ) AS Total_de_certificados
FROM certificate_issues, certificate, pgnzt_comprofiler, pgnzt_users, user, course
WHERE course.id =9
AND certificate.course = course.id
AND certificate.id = certificate_issues.certificateid
AND pgnzt_users.email = user.email
AND pgnzt_comprofiler.id = pgnzt_users.id
AND user.id = certificate_issues.userid
AND certificate_issues.timecreated
BETWEEN '1467331200'
AND '1483142400'
GROUP BY pgnzt_comprofiler.cb_escolaridade, pgnzt_comprofiler.cb_areadeformacao

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

17) Solicitação da Mariana de Brasilia


SELECT YEAR( from_unixtime( certificate_issues.timecreated ) ) AS Ano, pgnzt_comprofiler.cb_estado AS Estado, pgnzt_comprofiler.cb_municipio AS Cidade, course.fullname AS Curso, COUNT( certificate_issues.id ) Total
FROM pgnzt_comprofiler, certificate_issues, pgnzt_users, user, course, certificate
WHERE pgnzt_comprofiler.user_id = pgnzt_users.id
AND pgnzt_users.email = user.email
AND user.id = certificate_issues.userid
AND certificate.course = course.id
AND certificate.id = certificate_issues.certificateid
AND certificate_issues.timecreated BETWEEN '1420070400' AND '1451606399'
GROUP BY pgnzt_comprofiler.cb_municipio, course.fullname, pgnzt_comprofiler.cb_estado
ORDER BY `Cidade` ASC


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

18) Solicitação da Mariana de Brasilia 2

SELECT YEAR( from_unixtime( certificate_issues.timecreated ) ) AS Ano, MONTH( from_unixtime( certificate_issues.timecreated ) ) AS Mes, pgnzt_comprofiler.cb_estado AS Estado, pgnzt_comprofiler.cb_municipio AS Cidade, pgnzt_comprofiler.cb_escolaridade AS Escolaridade, pgnzt_comprofiler.cb_areadeformacao AS Area_de_Formacao, course.fullname AS Curso, COUNT( certificate_issues.id ) Total
FROM pgnzt_comprofiler, certificate_issues, pgnzt_users, user, course, certificate
WHERE pgnzt_comprofiler.user_id = pgnzt_users.id
AND pgnzt_users.email = user.email
AND user.id = certificate_issues.userid
AND certificate.course = course.id
AND certificate.id = certificate_issues.certificateid
AND certificate_issues.timecreated BETWEEN '1483228800' AND '1491004799' GROUP BY pgnzt_comprofiler.cb_municipio, course.fullname, pgnzt_comprofiler.cb_estado ORDER BY `Estado` ASC


-----------------------------------------------------------------------
19) Total de cadastrados por escolaridade

SELECT COUNT( pgnzt_users.id ) AS Total, pgnzt_comprofiler.cb_escolaridade AS Escolaridade
FROM pgnzt_users, pgnzt_comprofiler
WHERE pgnzt_users.registerDate BETWEEN '2015-01-01 00:00:00' AND '2015-12-31 00:00:00'
AND pgnzt_comprofiler.id = pgnzt_users.id
GROUP BY pgnzt_comprofiler.cb_escolaridade

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

20) Query da solicitação da Pamela de brasilia

SELECT YEAR( from_unixtime( certificate_issues.timecreated ) ) AS Ano, course.fullname AS Curso, pgnzt_comprofiler.cb_escolaridade AS Escolaridade, pgnzt_comprofiler.cb_areadeformacao AS Formacao, pgnzt_comprofiler.cb_tipodecadastro AS Ocupacao, COUNT( certificate_issues.id ) Total
FROM pgnzt_comprofiler, certificate_issues, pgnzt_users, user, course, certificate
WHERE pgnzt_comprofiler.user_id = pgnzt_users.id
AND pgnzt_users.email = user.email
AND user.id = certificate_issues.userid
AND certificate.course = course.id
AND certificate.id = certificate_issues.certificateid
AND certificate_issues.timecreated BETWEEN '1483228800' AND '1514678400'
GROUP BY course.fullname, Escolaridade, Formacao ORDER BY `Curso` ASC

------------------------------------------------------------------------
Ano de 2017:
01/01/2017 = 1483228800
31/12/2017 = 1514678400

Ano de 2016:
01/01/2016 = 1451606400
31/12/2016 = 1483142400

Ano de 2015:
01/01 = 1420070400
31/12 = 1451520000

Ano de 2014:
01/01 = 1388534400
31/12 = 1419984000

Ano de 2013:
01/01 = 1356998400
31/12 = 1388448000