----------------------------------------------------------------------------------
--
-- 01/04/2010
--
----------------------------------------------------------------------------------

CREATE TABLE "mat_turmaDisciplina"
(
   codturma integer NOT NULL, 
   codescola integer NOT NULL, 
   codprofessor bigint NOT NULL, 
   iddisciplina integer NOT NULL, 
   diasemana character(1) NOT NULL, 
   horario smallint NOT NULL, 
   CONSTRAINT pk_turmadisciplina PRIMARY KEY (codturma, codescola, codprofessor, iddisciplina, diasemana, horario), 
   CONSTRAINT fk_turmadisciplina_turma FOREIGN KEY (codturma, codescola) REFERENCES mat_turma (codturma, codescola) ON UPDATE NO ACTION ON DELETE NO ACTION, 
   CONSTRAINT fk_turmadisciplina_professor FOREIGN KEY (codprofessor) REFERENCES mat_professor (codprofessor) ON UPDATE NO ACTION ON DELETE NO ACTION, 
   CONSTRAINT fk_turmadisciplina_disciplina FOREIGN KEY (iddisciplina) REFERENCES mat_disciplina (iddisciplina) ON UPDATE NO ACTION ON DELETE NO ACTION
) 
WITH (
  OIDS = FALSE
)
;

CREATE OR REPLACE VIEW indicador_ed1_1 as
SELECT
	(mun.totalmun/tt.totalalunos)*100 as proporcao
FROM

	(
		SELECT
			CAST(count(*) as NUMERIC(10,2)) as totalmun
		FROM
			mat_aluno a INNER JOIN "mat_alunoEscola" ae
				ON (a.codaluno = ae."codAluno")
			INNER JOIN mat_escola e
				ON (ae."codEscola" = e.codescola)
		WHERE
			EXTRACT(YEAR FROM age(a.dtnascimento)) >= 2 AND EXTRACT(YEAR FROM age(a.dtnascimento)) <= 5
			AND e.coddependenciaadministrativa = 3
	) as mun,
	(
		SELECT
			CAST(count(*) as NUMERIC(10,2)) as totalalunos
		FROM
			mat_aluno a INNER JOIN "mat_alunoEscola" ae
				ON (a.codaluno = ae."codAluno")
			INNER JOIN mat_escola e
				ON (ae."codEscola" = e.codescola)
		WHERE
			EXTRACT(YEAR FROM age(a.dtnascimento)) >= 2 AND EXTRACT(YEAR FROM age(a.dtnascimento)) <= 5
		 
	) as tt;
	
CREATE OR REPLACE VIEW indicador_ed1_2 as
SELECT
	(mun.totalmun/tt.totalalunos)*100 as proporcao
FROM

	(
		SELECT
			CAST(count(*) as NUMERIC(10,2)) as totalmun
		FROM
			mat_aluno a INNER JOIN "mat_alunoEscola" ae
				ON (a.codaluno = ae."codAluno")
			INNER JOIN mat_escola e
				ON (ae."codEscola" = e.codescola)
		WHERE
			EXTRACT(YEAR FROM age(a.dtnascimento)) >= 6 AND EXTRACT(YEAR FROM age(a.dtnascimento)) <= 16
			AND e.coddependenciaadministrativa = 3
	) as mun,
	(
		SELECT
			CAST(count(*) as NUMERIC(10,2)) as totalalunos
		FROM
			mat_aluno a INNER JOIN "mat_alunoEscola" ae
				ON (a.codaluno = ae."codAluno")
			INNER JOIN mat_escola e
				ON (ae."codEscola" = e.codescola)
		WHERE
			EXTRACT(YEAR FROM age(a.dtnascimento)) >= 6 AND EXTRACT(YEAR FROM age(a.dtnascimento)) <= 16
		 
	) as tt;
	
CREATE OR REPLACE VIEW indicador_ed1 as
SELECT
	(mun.totalmun/tt.totalalunos)*100 as proporcao
FROM

	(
		SELECT
			CAST(count(*) as NUMERIC(10,2)) as totalmun
		FROM
			mat_aluno a INNER JOIN "mat_alunoEscola" ae
				ON (a.codaluno = ae."codAluno")
			INNER JOIN mat_escola e
				ON (ae."codEscola" = e.codescola)
		WHERE
			EXTRACT(YEAR FROM age(a.dtnascimento)) >= 2 AND EXTRACT(YEAR FROM age(a.dtnascimento)) <= 16
			AND e.coddependenciaadministrativa = 3
	) as mun,
	(
		SELECT
			CAST(count(*) as NUMERIC(10,2)) as totalalunos
		FROM
			mat_aluno a INNER JOIN "mat_alunoEscola" ae
				ON (a.codaluno = ae."codAluno")
			INNER JOIN mat_escola e
				ON (ae."codEscola" = e.codescola)
		WHERE
			EXTRACT(YEAR FROM age(a.dtnascimento)) >= 2 AND EXTRACT(YEAR FROM age(a.dtnascimento)) <= 16
		 
	) as tt;
	
CREATE OR REPLACE VIEW indicador_ed5 as
SELECT
	(profat.qtdativa/prof.qtdtotal)*100 as proporcao
FROM

	(
		SELECT
			CAST(count(distinct p.codprofessor) as NUMERIC(10,2)) as qtdativa
		FROM
			mat_professor p INNER JOIN "mat_turmaDisciplina" td
				ON (p.codprofessor = td.codprofessor)
	) as profat,
	(
		SELECT
			CAST(count(*) as NUMERIC(10,2)) as qtdtotal
		FROM
			mat_professor 
	) as prof
	
	
CREATE OR REPLACE VIEW estatisticacadalunodistribuicaoidadeSerie AS 
SELECT 
	date_part('year',age(a.dtnascimento)) AS idade, count(*) AS qtd,t.idnivelescolar
FROM 
	mat_aluno a INNER JOIN "mat_turmaAluno" ta
		ON (a.codaluno = ta.codaluno)
	INNER JOIN mat_turma t
		ON (ta.codturma = t.codturma AND ta.codescola = t.codescola)
WHERE 
	ta.dtresultado is null
GROUP BY date_part('year', age(a.dtnascimento)),t.idnivelescolar
ORDER BY date_part('year', age(a.dtnascimento)),t.idnivelescolar;

CREATE OR REPLACE VIEW estatisticacadalunodistribuicaoidadeSexo AS 
 SELECT date_part('year',age(mat_aluno.dtnascimento)) AS idade, count(*) AS qtd,sexo
   FROM mat_aluno
  GROUP BY date_part('year', age(mat_aluno.dtnascimento)),sexo
  ORDER BY date_part('year', age(mat_aluno.dtnascimento)),sexo;
	
----------------------------------------------------------------------------------
--
-- 07/04/2010
--
----------------------------------------------------------------------------------

CREATE TABLE "mat_funcionarioEscola"
(
   codfuncionario integer NOT NULL, 
   "login" character varying(20) NOT NULL, 
   nome character varying(100), 
   senha character(32) NOT NULL, 
   codescola integer NOT NULL, 
   CONSTRAINT "pk_funcionarioEscola" PRIMARY KEY (codfuncionario), 
   CONSTRAINT "fk_funcionarioEscola_escola" FOREIGN KEY (codescola) REFERENCES mat_escola (codescola) ON UPDATE NO ACTION ON DELETE NO ACTION
) 
WITH (
  OIDS = FALSE
)
;

CREATE TABLE "mat_funcionarioCoordenadoria"
(
   codfuncionario integer NOT NULL, 
   "login" character varying(20) NOT NULL, 
   nome character varying(100) NOT NULL, 
   senha character(32) NOT NULL, 
   codcoordenadoria integer NOT NULL, 
   CONSTRAINT pk_funcionariocoordenadoria PRIMARY KEY (codfuncionario), 
   CONSTRAINT un_login UNIQUE ("login"), 
   CONSTRAINT "fk_funcionarioCoordenadoria_coordenadoria" FOREIGN KEY (codcoordenadoria) REFERENCES mat_coordenadoria ("codCoordenadoria") ON UPDATE NO ACTION ON DELETE NO ACTION
) 
WITH (
  OIDS = FALSE
)
;

ALTER TABLE "mat_funcionarioEscola" ADD CONSTRAINT "un_login_funcionarioEscola" UNIQUE ("login");


----------------------------------------------------------------------------------
--
-- 08/04/2010
--
----------------------------------------------------------------------------------

CREATE TABLE mat_professordisciplina
(
   codprofessor bigint NOT NULL,
   iddisciplina integer NOT NULL,
   CONSTRAINT pk_professordisciplina PRIMARY KEY (codprofessor, iddisciplina),
   CONSTRAINT fk_professordisciplina_professor FOREIGN KEY (codprofessor) REFERENCES mat_professor (codprofessor) ON UPDATE NO ACTION ON DELETE NO ACTION,
   CONSTRAINT fk_professordisciplina_disciplina FOREIGN KEY (iddisciplina) REFERENCES mat_disciplina (iddisciplina) ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS = FALSE
)
; 		

----------------------------------------------------------------------------------
--
-- 14/04/2010
--
----------------------------------------------------------------------------------	
	
ALTER TABLE municipio ADD CONSTRAINT un_municipio UNIQUE (codigomunicipio, codigoestado);	
	
ALTER TABLE mat_aluno ADD COLUMN escolaanterioruf numeric(2,0) DEFAULT null;
ALTER TABLE mat_aluno ADD COLUMN escolaanteriorcidade numeric(7,0) DEFAULT null;
ALTER TABLE mat_aluno ADD CONSTRAINT fk_aluno_escolaanterior FOREIGN KEY (escolaanterioruf, escolaanteriorcidade) REFERENCES municipio (codigoestado, codigomunicipio) ON UPDATE NO ACTION ON DELETE NO ACTION;

----------------------------------------------------------------------------------
--
-- 18/04/2010
--
----------------------------------------------------------------------------------	

ALTER TABLE mat_endereco ADD COLUMN "semEnderecoFixo" integer DEFAULT -1;

----------------------------------------------------------------------------------
--
-- 26/04/2010
--
----------------------------------------------------------------------------------	


ALTER TABLE "mat_alunoEscola"
   ADD COLUMN "escolaridadealuno" numeric(3,0) DEFAULT 1;
   
ALTER TABLE "mat_alunoEscola" ADD CONSTRAINT "fk_alunoEscolaEscolaridade" FOREIGN KEY (escolaridadealuno) REFERENCES escolaridadealuno (codigoescolaridade) ON UPDATE NO ACTION ON DELETE NO ACTION;

----------------------------------------------------------------------------------
--
-- 04/05/2010
--
----------------------------------------------------------------------------------	
INSERT INTO mat_horario(
            idhorario, nomehorario)
    VALUES (1, '1');
INSERT INTO mat_horario(
            idhorario, nomehorario)
    VALUES (2, '2');
INSERT INTO mat_horario(
            idhorario, nomehorario)
    VALUES (3, '3');
INSERT INTO mat_horario(
            idhorario, nomehorario)
    VALUES (4, '4');
INSERT INTO mat_horario(
            idhorario, nomehorario)
    VALUES (5, '5');
INSERT INTO mat_horario(
            idhorario, nomehorario)
    VALUES (6, '6');
INSERT INTO mat_horario(
            idhorario, nomehorario)
    VALUES (7, 'Único');

ALTER TABLE "mat_turmaDisciplina" ALTER diasemana TYPE character(2);

----------------------------------------------------------------------------------
--
-- 06/05/2010
--
----------------------------------------------------------------------------------	

CREATE TABLE mat_metodoavaliacao
(
   idmetodo integer NOT NULL, 
   descricaometodo character varying(25) NOT NULL, 
   CONSTRAINT pk_metodoavaliacao PRIMARY KEY (idmetodo)
) 
WITH (
  OIDS = FALSE
)
;

INSERT INTO mat_metodoavaliacao(
            idmetodo, descricaometodo)
    VALUES (1, 'De 0 a 100');

INSERT INTO mat_metodoavaliacao(
            idmetodo, descricaometodo)
    VALUES (2, 'De A a E');
    
ALTER TABLE "mat_turmaDisciplina" ADD COLUMN metodoavaliacao integer NOT NULL DEFAULT 1;
ALTER TABLE "mat_turmaDisciplina" ADD CONSTRAINT fk_turmadisciplina_metodoavaliacao FOREIGN KEY (metodoavaliacao) REFERENCES mat_metodoavaliacao (idmetodo) ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE;


CREATE TABLE "mat_avaliacaoTurma"
(
   codescola integer NOT NULL, 
   codturma integer NOT NULL, 
   iddisciplina integer NOT NULL, 
   codaluno bigint NOT NULL, 
   bimestre smallint NOT NULL DEFAULT 1, 
   nota character varying(3) NOT NULL, 
   CONSTRAINT pk_avaliacao PRIMARY KEY (codescola, codturma, iddisciplina, codaluno, bimestre), 
   CONSTRAINT "fk_avaliacaoAluno" FOREIGN KEY (codescola, codturma, codaluno) REFERENCES "mat_turmaAluno" (codescola, codturma, codaluno) ON UPDATE NO ACTION ON DELETE NO ACTION
) 
WITH (
  OIDS = FALSE
)
;
COMMENT ON TABLE "mat_avaliacaoTurma" IS 'tabela que contém as informações das notas dos alunos de uma determinada turma

A coluna bimestre deve ser numerada de 1 a 4';

CREATE TABLE mat_situacaofinal
(
   idsituacao integer NOT NULL, 
   descricaosituacao character varying(100) NOT NULL, 
   CONSTRAINT pk_situacaofinal PRIMARY KEY (idsituacao)
) 
WITH (
  OIDS = FALSE
)
;

CREATE TABLE mat_turmadisciplinasituacaofinal
(
   codescola integer NOT NULL, 
   codturma integer NOT NULL, 
   iddisciplina integer NOT NULL, 
   codaluno bigint NOT NULL, 
   idsituacaofinal integer NOT NULL, 
   CONSTRAINT pk_turmadisciplinasituacaofinal PRIMARY KEY (codescola, codturma, iddisciplina, codaluno), 
   CONSTRAINT fk_turmadisciplinasituacaofinalaluno FOREIGN KEY (codescola, codturma, codaluno) REFERENCES "mat_turmaAluno" (codescola, codturma, codaluno) ON UPDATE NO ACTION ON DELETE NO ACTION, 
   CONSTRAINT "fk_turmaDisciplina_situacaofinal" FOREIGN KEY (idsituacaofinal) REFERENCES mat_situacaofinal (idsituacao) ON UPDATE NO ACTION ON DELETE NO ACTION
) 
WITH (
  OIDS = FALSE
)
;

INSERT INTO mat_situacaofinal(
            idsituacao, descricaosituacao)
    VALUES (1, 'Aprovado');
INSERT INTO mat_situacaofinal(
            idsituacao, descricaosituacao)
    VALUES (2, 'Reprovado');
        

----------------------------------------------------------------------------------
--
-- 07/05/2010
--
----------------------------------------------------------------------------------	
ALTER TABLE "mat_turma" ADD COLUMN anoturma integer NOT NULL DEFAULT date_part('year', now());

----------------------------------------------------------------------------------
--
-- 10/05/2010
--

----------------------------------------------------------------------------------
--
-- 13/05/2010
--
----------------------------------------------------------------------------------	

CREATE TABLE "mat_presencaTurma"
(
  codescola integer NOT NULL,
  codturma integer NOT NULL,
  codaluno bigint NULL,
  codprofessor bigint NULL,
  datapresenca date NOT NULL,
  horario smallint NOT NULL,
  CONSTRAINT pk_presenca PRIMARY KEY (codescola, codturma, codaluno, codprofessor, datapresenca,horario),
  CONSTRAINT "fk_alunoPresenca" FOREIGN KEY (codescola, codturma, codaluno)
      REFERENCES "mat_turmaAluno" (codescola, codturma, codaluno) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "fk_professorPresenca" FOREIGN KEY (codprofessor)
      REFERENCES "mat_professor" (codprofessor) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION      
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "mat_presencaTurma" OWNER TO postgres;
COMMENT ON TABLE "mat_presentaTurma anota a informação de presença de todos os alunos da turma e do professor";
----------------------------------------------------------------------------------
--
-- 19/05/2010
--
----------------------------------------------------------------------------------	

CREATE TABLE mat_turnoturma
(
   idturno smallint NOT NULL, 
   nometurno character varying(50) NOT NULL, 
   CONSTRAINT pk_turnoturma PRIMARY KEY (idturno)
) 
WITH (
  OIDS = FALSE
)
;

INSERT INTO mat_turnoturma(
            idturno, nometurno)
    VALUES (1, 'Manhã');
INSERT INTO mat_turnoturma(
            idturno, nometurno)
    VALUES (2, 'Tarde');
INSERT INTO mat_turnoturma(
            idturno, nometurno)
    VALUES (3, 'Noite');
	
ALTER TABLE mat_turma ADD COLUMN turnoturma smallint NOT NULL DEFAULT 1;
ALTER TABLE mat_turma ADD CONSTRAINT fk_turnoturma FOREIGN KEY (turnoturma) REFERENCES mat_turnoturma (idturno) ON UPDATE NO ACTION ON DELETE NO ACTION;
	
----------------------------------------------------------------------------------
--
-- 21/05/2010
--
----------------------------------------------------------------------------------

ALTER TABLE "mat_presencaTurma" DROP CONSTRAINT pk_presenca;
ALTER TABLE "mat_presencaTurma"
   ALTER COLUMN codaluno DROP NOT NULL;
ALTER TABLE "mat_presencaTurma"
   ALTER COLUMN codprofessor DROP NOT NULL;
ALTER TABLE "mat_presencaTurma" ADD COLUMN idpresenca bigserial NOT NULL;
ALTER TABLE "mat_presencaTurma" ADD CONSTRAINT pk_presenca PRIMARY KEY (idpresenca);

ALTER TABLE "mat_presencaTurma" ADD CONSTRAINT un_presenca UNIQUE (codescola, codturma, codaluno, codprofessor, datapresenca, horario);


----------------------------------------------------------------------------------
--
-- 25/05/2010
--
----------------------------------------------------------------------------------

CREATE OR REPLACE VIEW educacao.geoiguacu
AS
	SELECT
		id as geoiguacu,
		idtipo_logradouro as codigotipologradourogeoiguacu,
		logradouro as logradourogeoiguacu,
		numero as numerogeoiguacu,
		complemento as complementogeoiguacu,
		bairro as bairrogeoiguacu,
		cep as cepgeoiguacu
	FROM 
		cabcomum.geoiguacu

----------------------------------------------------------------------------------
--
-- 28/05/2010
--
----------------------------------------------------------------------------------
ALTER TABLE educacao.escolaridadealuno ADD COLUMN codigoproximaescolaridade numeric(3,0) DEFAULT null;

ALTER TABLE educacao.escolaridadealuno ADD COLUMN ordem smallint;

UPDATE educacao.escolaridadealuno SET descricaoescolaridade = 'Infantil 2', codigoproximaescolaridade = 3, ordem = 1 WHERE codigoescolaridade = 1;
UPDATE educacao.escolaridadealuno SET descricaoescolaridade = 'Infantil 3', codigoproximaescolaridade = 4, ordem = 2 WHERE codigoescolaridade = 3;
UPDATE educacao.escolaridadealuno SET descricaoescolaridade = 'Infantil 4', codigoproximaescolaridade = 5, ordem = 3 WHERE codigoescolaridade = 4;
UPDATE educacao.escolaridadealuno SET descricaoescolaridade = 'Infantil 5', codigoproximaescolaridade = 2, ordem = 4 WHERE codigoescolaridade = 5;
UPDATE educacao.escolaridadealuno SET descricaoescolaridade = '1º série', codigoproximaescolaridade = 7, ordem = 5 WHERE codigoescolaridade = 2;
UPDATE educacao.escolaridadealuno SET descricaoescolaridade = '2º série', codigoproximaescolaridade = 8, ordem = 6 WHERE codigoescolaridade = 7;
UPDATE educacao.escolaridadealuno SET descricaoescolaridade = '3º série', codigoproximaescolaridade = 9, ordem = 7 WHERE codigoescolaridade = 8;
UPDATE educacao.escolaridadealuno SET descricaoescolaridade = '4º série', codigoproximaescolaridade = 6, ordem = 8 WHERE codigoescolaridade = 9;
UPDATE educacao.escolaridadealuno SET descricaoescolaridade = '5º série', codigoproximaescolaridade = 10, ordem = 9 WHERE codigoescolaridade = 6;
UPDATE educacao.escolaridadealuno SET descricaoescolaridade = '6º série', codigoproximaescolaridade = 11, ordem = 10 WHERE codigoescolaridade = 10;
UPDATE educacao.escolaridadealuno SET descricaoescolaridade = '7º série', codigoproximaescolaridade = 12, ordem = 11 WHERE codigoescolaridade = 11;

-- aqui insere
INSERT INTO educacao.escolaridadealuno values(12,'8º série',13,12);
INSERT INTO educacao.escolaridadealuno values(13,'9º série',null,13);
INSERT INTO educacao.escolaridadealuno values(14,'EJA 1',15,14);
INSERT INTO educacao.escolaridadealuno values(15,'EJA 2',16,15);
INSERT INTO educacao.escolaridadealuno values(16,'EJA 3',17,16);
INSERT INTO educacao.escolaridadealuno values(17,'EJA 4',18,17);
INSERT INTO educacao.escolaridadealuno values(18,'EJA 5',19,18);
INSERT INTO educacao.escolaridadealuno values(19,'EJA 6',20,19);
INSERT INTO educacao.escolaridadealuno values(20,'EJA 7',21,20);
INSERT INTO educacao.escolaridadealuno values(21,'EJA 8',null,21);	

drop table educacao.mat_nivelescolar cascade;

CREATE OR REPLACE VIEW educacao.mat_nivelescolar 
AS
SELECT
	codigoescolaridade as idnivel, descricaoescolaridade as nomenivel, codigoproximaescolaridade, ordem
FROM
	educacao.escolaridadealuno
	
----------------------------------------------------------------------------------
--
-- 31/05/2010
--
----------------------------------------------------------------------------------
	
ALTER TABLE educacao.mat_endereco
   ALTER COLUMN geoiguacu DROP NOT NULL;
	
----------------------------------------------------------------------------------
--
-- 06/06/2010
--
----------------------------------------------------------------------------------

UPDATE educacao.mat_horario SET nomehorario = 7 WHERE idhorario = 7;
INSERT INTO educacao.mat_horario(idhorario, nomehorario) VALUES (8, 8);
INSERT INTO educacao.mat_horario(idhorario, nomehorario) VALUES (9, 'Único');

----------------------------------------------------------------------------------
--
-- 07/06/2010
--
----------------------------------------------------------------------------------

CREATE TABLE educacao.mat_turnohorario
(
   idturno integer NOT NULL, 
   idhorario integer NOT NULL, 
   CONSTRAINT fk_horario_turno FOREIGN KEY (idturno) REFERENCES educacao.mat_turnoturma (idturno) ON UPDATE NO ACTION ON DELETE NO ACTION, 
   CONSTRAINT fk_turno_horario FOREIGN KEY (idhorario) REFERENCES educacao.mat_horario (idhorario) ON UPDATE NO ACTION ON DELETE NO ACTION, 
   CONSTRAINT pk_turnohorario PRIMARY KEY (idturno, idhorario)
) 
WITH (
  OIDS = FALSE
)
;


CREATE TABLE educacao.mat_log
(
   seqlog bigserial NOT NULL, 
   consulta text NOT NULL, 
   data timestamp NOT NULL DEFAULT current_timestamp, 
   usuario character varying(50) NOT NULL, 
   erro boolean NOT NULL DEFAULT false, 
   msgerro text DEFAULT null, 
   CONSTRAINT pk_log PRIMARY KEY (seqlog)
) 
WITH (
  OIDS = FALSE
)
;
CREATE INDEX in_erro
   ON educacao.mat_log (data ASC NULLS LAST, erro ASC NULLS LAST);
CREATE INDEX in_log_data
   ON educacao.mat_log (data ASC NULLS LAST);

----------------------------------------------------------------------------------
--
-- 17/06/2010
--
----------------------------------------------------------------------------------

--primeira chave nao pode ser excluida por causa de referencia em outra tabela
DELETE FROM educacao.mat_turnoturma WHERE idturno > 1;

--criando novos turnos
INSERT INTO educacao.mat_turnoturma(idturno, nometurno) VALUES (2, 'Manhã Estendido');
INSERT INTO educacao.mat_turnoturma(idturno, nometurno) VALUES (3, 'Tarde');
INSERT INTO educacao.mat_turnoturma(idturno, nometurno) VALUES (4, 'Tarde Estendido');
INSERT INTO educacao.mat_turnoturma(idturno, nometurno) VALUES (5, 'Integral');
INSERT INTO educacao.mat_turnoturma(idturno, nometurno) VALUES (6, 'Noite');

--deleta registros de horarios
DELETE FROM educacao.mat_turnohorario;

UPDATE educacao.mat_horario SET nomehorario = 9 WHERE idhorario = 9;
INSERT INTO educacao.mat_horario(idhorario, nomehorario) VALUES (10, 10);
INSERT INTO educacao.mat_horario(idhorario, nomehorario) VALUES (11, 11);
INSERT INTO educacao.mat_horario(idhorario, nomehorario) VALUES (12, 12);
INSERT INTO educacao.mat_horario(idhorario, nomehorario) VALUES (13, 13);
INSERT INTO educacao.mat_horario(idhorario, nomehorario) VALUES (14, 14);
INSERT INTO educacao.mat_horario(idhorario, nomehorario) VALUES (15, 15);
INSERT INTO educacao.mat_horario(idhorario, nomehorario) VALUES (16, 16);
INSERT INTO educacao.mat_horario(idhorario, nomehorario) VALUES (17, 17);

--relacoes de turnos com horarios
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (1, 1);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (1, 2);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (1, 3);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (1, 4);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (1, 5);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (1, 6);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (2, 1);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (2, 2);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (2, 3);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (2, 4);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (2, 5);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (2, 6);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (2, 7);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (2, 8);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (2, 9);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (3, 7);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (3, 8);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (3, 9);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (3, 10);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (3, 11);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (3, 12);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (4, 4);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (4, 5);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (4, 6);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (4, 7);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (4, 8);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (4, 9);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (4, 10);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (4, 11);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (4, 12);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (5, 1);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (5, 2);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (5, 3);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (5, 4);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (5, 5);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (5, 6);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (5, 7);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (5, 8);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (5, 9);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (5, 10);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (5, 11);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (5, 12);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (6, 13);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (6, 14);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (6, 15);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (6, 16);
INSERT INTO educacao.mat_turnohorario(idturno, idhorario) VALUES (6, 17);

----------------------------------------------------------------------------------
--
-- 22/06/2010
--
----------------------------------------------------------------------------------

ALTER TABLE educacao."mat_alunoEspecifico"
   ADD COLUMN escolamunicipal bigint;

ALTER TABLE educacao."mat_alunoEspecificoTmp"
   ADD COLUMN escolamunicipal bigint;

ALTER TABLE educacao."mat_alunoEspecifico" ADD CONSTRAINT fk_escolamunicipal FOREIGN KEY (escolamunicipal)
      REFERENCES educacao.mat_escola (codescola) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE educacao."mat_alunoEspecificoTmp" ADD CONSTRAINT fk_escolamunicipaltmp FOREIGN KEY (escolamunicipal)
      REFERENCES educacao.mat_escola (codescola) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;

CREATE INDEX fki_escolamunicipaltmp
  ON educacao."mat_alunoEspecificoTmp"
  USING btree
  (escolamunicipal);

CREATE INDEX fki_escolamunicipal
  ON educacao."mat_alunoEspecifico"
  USING btree
  (escolamunicipal);

----------------------------------------------------------------------------------
--
-- 23/06/2010
--
------------------------------------------------------------------------------------ 

CREATE OR REPLACE VIEW educacao.mat_aluno AS 
 SELECT a.codaluno, a.blprimeiramatricula, a.dtdeferido, p.nome AS nomealuno, a.pathfotoaluno, pdc.numero AS cadsus, p.idsexo AS sexo, p.data_nascimento AS dtnascimento, p.idestado_nascimento AS ufnascimento, p.idmunicipio_nascimento AS municipionascimento, p.idpais_nascimento AS paisorigemaluno, pdce.cartorio AS certidaocartorio, pdce.termo AS certidaotermo, pdce.folha AS certidaofolha, pdce.livro AS certidaolivro, pdce.data_emissao AS certidaoemissao, pdce.idestado AS certidaoestado, pdce.idtipo_certidao AS certidaotipo, pdce.matricula_certidao AS certidaomatricula, 
        CASE
            WHEN pdce.apresentado = '1'::"char" THEN true
            ELSE false
        END AS blcertidaocomprovada, 
        CASE
            WHEN pdce.img_documento_frente IS NULL THEN false
            ELSE true
        END AS blcertidaodigitalizada, pdce.img_documento_frente AS pathcertidaofrente, pdce.img_documento_verso AS pathcertidaoverso, pdcc.numero AS cpf, 
        CASE
            WHEN pdcc.apresentado = '1'::"char" THEN true
            ELSE false
        END AS blcpfcomprovado, 
        CASE
            WHEN pdcc.img_documento_frente IS NULL THEN false
            ELSE true
        END AS blcpfdigitalizado, pdn.numero AS nis, 
        CASE
            WHEN pdn.apresentado = '1'::"char" THEN true
            ELSE false
        END AS blniscomprovado, 
        CASE
            WHEN pdn.img_documento_frente IS NULL THEN false
            ELSE true
        END AS blnisdigitalizado, pdcc.img_documento_frente AS pathcpffrente, pdcc.img_documento_verso AS pathcpfverso, pdn.img_documento_frente AS pathnisfrente, pdn.img_documento_verso AS pathnisverso, a.inep, a.blnecessidadesespeciais, p.idraca AS cor, a.dtingresso, a.blalunotransferencia, a.tpescolaorigem, a.nomeescolaorigem, a.escolaorigemoutros, p.beneficiario_bolsa_familia AS blbolsafamilia, a.responsavelmatricula, p.fone_residencial AS telresidencial, p.fone_celular AS telcelular, p.fone_recado AS telrecado, p.email, 
        CASE
            WHEN pec.idtipo_endereco IS NULL THEN false
            ELSE true
        END AS blenderecocorrespondencia, a.nomeescolaanterior, a.codigoescolaanterior, a.bairroescolaanterior, a.tipoescolaanterior, a.ultimaescolaridadealuno, a.blaprovadoultimaescolaridade, a.turmaanterior, a.turnoanterior, a.blcompravoescolaridadeanterior, a.blparticipouhorariointegral, a.turnopreferencia, a.bldesejahorariointegral, a.blalunopasseescolar, a.blresponsavelpasseescolar, a."blPaiTemDocumentos", a."nomePai", a."blMaeTemDocumentos", p.nome_mae AS "nomeMae", a."blResponsavelTemDocumentos", a."nomeResponsavel", a."dadoCenso", a."grauParentescoResponsavel", a.blescolaanteriornomunicipio, a.escolaanterioruf, a.escolaanteriorcidade, p.idnacionalidade AS nacionalidadealuno, a.idpessoa, a.escolamunicipal
   FROM educacao."mat_alunoEspecifico" a
   JOIN cabcomum.pessoa p ON a.idpessoa = p.id
   LEFT JOIN cabcomum.pessoa_documento pdc ON p.id = pdc.idpessoa AND pdc.idtipo_documento = 6
   LEFT JOIN cabcomum.pessoa_documento pdce ON p.id = pdce.idpessoa AND pdce.idtipo_documento = 9
   LEFT JOIN cabcomum.pessoa_documento pdcc ON p.id = pdcc.idpessoa AND pdcc.idtipo_documento = 5
   LEFT JOIN cabcomum.pessoa_documento pdn ON p.id = pdn.idpessoa AND pdn.idtipo_documento = 4
   LEFT JOIN cabcomum.pessoa_endereco pec ON p.id = pec.idpessoa AND pec.idtipo_endereco = 2;

----------------------------------------------------------------------------------
--
-- 29/06/2010
--
------------------------------------------------------------------------------------ 

--adicionando coluna que indica se a linha foi removida ou nao
ALTER TABLE educacao."mat_funcionarioEscola" ADD removido boolean NOT NULL DEFAULT false;
ALTER TABLE educacao."mat_funcionarioCoordenadoria" ADD removido boolean NOT NULL DEFAULT false;
ALTER TABLE educacao.mat_turma ADD removido boolean NOT NULL DEFAULT false;
ALTER TABLE educacao.mat_escola ADD removido boolean NOT NULL DEFAULT false;
ALTER TABLE educacao.mat_coordenadoria ADD removido boolean NOT NULL DEFAULT false;
--ainda falta aluno, professor e matriz


----------------------------------------------------------------------------------
--
-- 06/07/2010
--
------------------------------------------------------------------------------------ 

--adicionadas duas colunas a tabela educacao."mat_presencaTurma", faltaaluno e faltaprofessor

ALTER TABLE educacao."mat_presencaTurma" ADD COLUMN faltaaluno boolean NOT NULL ;
ALTER TABLE educacao."mat_presencaTurma" ADD COLUMN faltaprofessor boolean NOT NULL ;

----------------------------------------------------------------------------------
--
-- 12/07/2010
--
----------------------------------------------------------------------------------

UPDATE educacao.mat_horario SET nomehorario = 'M1' WHERE idhorario = 1;
UPDATE educacao.mat_horario SET nomehorario = 'M2' WHERE idhorario = 2;
UPDATE educacao.mat_horario SET nomehorario = 'M3' WHERE idhorario = 3;
UPDATE educacao.mat_horario SET nomehorario = 'M4' WHERE idhorario = 4;
UPDATE educacao.mat_horario SET nomehorario = 'M5' WHERE idhorario = 5;
UPDATE educacao.mat_horario SET nomehorario = 'M6' WHERE idhorario = 6;
UPDATE educacao.mat_horario SET nomehorario = 'T1' WHERE idhorario = 7;
UPDATE educacao.mat_horario SET nomehorario = 'T2' WHERE idhorario = 8;
UPDATE educacao.mat_horario SET nomehorario = 'T3' WHERE idhorario = 9;
UPDATE educacao.mat_horario SET nomehorario = 'T4' WHERE idhorario = 10;
UPDATE educacao.mat_horario SET nomehorario = 'T5' WHERE idhorario = 11;
UPDATE educacao.mat_horario SET nomehorario = 'T6' WHERE idhorario = 12;
UPDATE educacao.mat_horario SET nomehorario = 'N1' WHERE idhorario = 13;
UPDATE educacao.mat_horario SET nomehorario = 'N2' WHERE idhorario = 14;
UPDATE educacao.mat_horario SET nomehorario = 'N3' WHERE idhorario = 15;
UPDATE educacao.mat_horario SET nomehorario = 'N4' WHERE idhorario = 16;
UPDATE educacao.mat_horario SET nomehorario = 'N5' WHERE idhorario = 17;


----------------------------------------------------------------------------------
--
-- 14/07/2010
--
----------------------------------------------------------------------------------

UPDATE educacao.escolaridadealuno SET descricaoescolaridade = '1º ano' WHERE descricaoescolaridade = '1º série';
UPDATE educacao.escolaridadealuno SET descricaoescolaridade = '2º ano' WHERE descricaoescolaridade = '2º série';
UPDATE educacao.escolaridadealuno SET descricaoescolaridade = '3º ano' WHERE descricaoescolaridade = '3º série';
UPDATE educacao.escolaridadealuno SET descricaoescolaridade = '4º ano' WHERE descricaoescolaridade = '4º série';
UPDATE educacao.escolaridadealuno SET descricaoescolaridade = '5º ano' WHERE descricaoescolaridade = '5º série';
UPDATE educacao.escolaridadealuno SET descricaoescolaridade = '6º ano' WHERE descricaoescolaridade = '6º série';
UPDATE educacao.escolaridadealuno SET descricaoescolaridade = '7º ano' WHERE descricaoescolaridade = '7º série';
UPDATE educacao.escolaridadealuno SET descricaoescolaridade = '8º ano' WHERE descricaoescolaridade = '8º série';
UPDATE educacao.escolaridadealuno SET descricaoescolaridade = '9º ano' WHERE descricaoescolaridade = '9º série';

----------------------------------------------------------------------------------
--
-- 16/07/2010
--
----------------------------------------------------------------------------------

-- View: educacao.escola

-- DROP VIEW educacao.escola;

CREATE OR REPLACE VIEW educacao.escola AS 
 SELECT e.codescola AS codigoescola, e.nomeescola, COALESCE(n.bairro, 'SEM ENDEREÇO DEFINIDO'::character varying) AS bairroescola
   FROM educacao.mat_escola e
   LEFT JOIN educacao.mat_endereco n ON e.codescola = n.identificacao AND n."tipoIdentificacao" = 3
   WHERE e.coddependenciaadministrativa = 3;

ALTER TABLE educacao.escola OWNER TO postgres;


-- View: educacao.estatisticacadalunodistribuicaoescola

-- DROP VIEW educacao.estatisticacadalunodistribuicaoescola;

CREATE OR REPLACE VIEW educacao.estatisticacadalunodistribuicaoescola AS 
 SELECT e.nomeescola, count(a."codAluno") AS qtdalunos
   FROM educacao.mat_escola e
   LEFT JOIN educacao."mat_alunoEscolaTMP" a ON e.codescola = a."codEscola"
  WHERE a."escolaAtual" = true AND e.coddependenciaadministrativa = 3
  GROUP BY e.nomeescola, e.codescola
  ORDER BY e.nomeescola;

ALTER TABLE educacao.estatisticacadalunodistribuicaoescola OWNER TO postgres;

----------------------------------------------------------------------------------
--
-- 19/07/2010
--
----------------------------------------------------------------------------------

CREATE OR REPLACE VIEW educacao.mat_aluno AS 
 SELECT a.codaluno, a.blprimeiramatricula, a.dtdeferido, p.nome AS nomealuno, a.pathfotoaluno, pdc.numero AS cadsus, p.idsexo AS sexo, p.data_nascimento AS dtnascimento, p.idestado_nascimento AS ufnascimento, p.idmunicipio_nascimento AS municipionascimento, p.idpais_nascimento AS paisorigemaluno, pdce.cartorio AS certidaocartorio, pdce.termo AS certidaotermo, pdce.folha AS certidaofolha, pdce.livro AS certidaolivro, pdce.data_emissao AS certidaoemissao, pdce.idestado AS certidaoestado, pdce.idtipo_certidao AS certidaotipo, pdce.matricula_certidao AS certidaomatricula, 
        CASE
            WHEN pdce.apresentado = '1'::"char" THEN true
            ELSE false
        END AS blcertidaocomprovada, 
        CASE
            WHEN pdce.img_documento_frente IS NULL THEN false
            ELSE true
        END AS blcertidaodigitalizada, pdce.img_documento_frente AS pathcertidaofrente, pdce.img_documento_verso AS pathcertidaoverso, pdcc.numero AS cpf, 
        CASE
            WHEN pdcc.apresentado = 't'::"char" THEN true
            ELSE false
        END AS blcpfcomprovado, 
        CASE
            WHEN pdcc.img_documento_frente IS NULL THEN false
            ELSE true
        END AS blcpfdigitalizado, pdn.numero AS nis, 
        CASE
            WHEN pdn.apresentado = 't'::"char" THEN true
            ELSE false
        END AS blniscomprovado, 
        CASE
            WHEN pdn.img_documento_frente IS NULL THEN false
            ELSE true
        END AS blnisdigitalizado, pdcc.img_documento_frente AS pathcpffrente, pdcc.img_documento_verso AS pathcpfverso, pdn.img_documento_frente AS pathnisfrente, pdn.img_documento_verso AS pathnisverso, a.inep, a.blnecessidadesespeciais, p.idraca AS cor, a.dtingresso, a.blalunotransferencia, a.tpescolaorigem, a.nomeescolaorigem, a.escolaorigemoutros, p.beneficiario_bolsa_familia AS blbolsafamilia, a.responsavelmatricula, p.fone_residencial AS telresidencial, p.fone_celular AS telcelular, p.fone_recado AS telrecado, p.email, 
        CASE
            WHEN pec.idtipo_endereco IS NULL THEN false
            ELSE true
        END AS blenderecocorrespondencia, a.nomeescolaanterior, a.codigoescolaanterior, a.bairroescolaanterior, a.tipoescolaanterior, a.ultimaescolaridadealuno, a.blaprovadoultimaescolaridade, a.turmaanterior, a.turnoanterior, a.blcompravoescolaridadeanterior, a.blparticipouhorariointegral, a.turnopreferencia, a.bldesejahorariointegral, a.blalunopasseescolar, a.blresponsavelpasseescolar, a."blPaiTemDocumentos", a."nomePai", a."blMaeTemDocumentos", p.nome_mae AS "nomeMae", a."blResponsavelTemDocumentos", a."nomeResponsavel", a."dadoCenso", a."grauParentescoResponsavel", a.blescolaanteriornomunicipio, a.escolaanterioruf, a.escolaanteriorcidade, p.idnacionalidade AS nacionalidadealuno, a.idpessoa, a.escolamunicipal
   FROM educacao."mat_alunoEspecifico" a
   JOIN cabcomum.pessoa p ON a.idpessoa = p.id
   LEFT JOIN cabcomum.pessoa_documento pdc ON p.id = pdc.idpessoa AND pdc.idtipo_documento = 6
   LEFT JOIN cabcomum.pessoa_documento pdce ON p.id = pdce.idpessoa AND pdce.idtipo_documento = 9
   LEFT JOIN cabcomum.pessoa_documento pdcc ON p.id = pdcc.idpessoa AND pdcc.idtipo_documento = 5
   LEFT JOIN cabcomum.pessoa_documento pdn ON p.id = pdn.idpessoa AND pdn.idtipo_documento = 4
   LEFT JOIN cabcomum.pessoa_endereco pec ON p.id = pec.idpessoa AND pec.idtipo_endereco = 2;

ALTER TABLE educacao.mat_aluno OWNER TO postgres;


----------------------------------------------------------------------------------
--
-- 22/07/2010
--
----------------------------------------------------------------------------------

ALTER TABLE educacao."mat_alunoEspecifico"
   ADD COLUMN tpsanguineo character varying(10);

ALTER TABLE educacao."mat_alunoEspecificoTmp"
   ADD COLUMN tpsanguineo character varying(10);

ALTER TABLE educacao."mat_alunoEspecifico"
   ADD COLUMN bltpsanguecomprovado boolean;

ALTER TABLE educacao."mat_alunoEspecificoTmp"
   ADD COLUMN bltpsanguedigitalizado boolean;

ALTER TABLE educacao."mat_alunoEspecifico"
   ADD COLUMN bltpsanguedigitalizado boolean;

ALTER TABLE educacao."mat_alunoEspecificoTmp"
   ADD COLUMN bltpsanguecomprovado boolean;

ALTER TABLE educacao."mat_alunoEspecifico"
   ADD COLUMN pathtpsanguefrente character varying(256);

ALTER TABLE educacao."mat_alunoEspecificoTmp"
   ADD COLUMN pathtpsanguefrente character varying(256);

ALTER TABLE educacao."mat_alunoEspecifico"
   ADD COLUMN pathtpsangueverso character varying(256);

ALTER TABLE educacao."mat_alunoEspecificoTmp"
   ADD COLUMN pathtpsangueverso character varying(256);


----------------------------------------------------------------------------------
--
-- 23/07/2010
--
----------------------------------------------------------------------------------

CREATE OR REPLACE VIEW educacao.mat_aluno AS 
 SELECT a.codaluno, a.blprimeiramatricula, a.dtdeferido, p.nome AS nomealuno, a.pathfotoaluno, pdc.numero AS cadsus, p.idsexo AS sexo, p.data_nascimento AS dtnascimento, p.idestado_nascimento AS ufnascimento, p.idmunicipio_nascimento AS municipionascimento, p.idpais_nascimento AS paisorigemaluno, pdce.cartorio AS certidaocartorio, pdce.termo AS certidaotermo, pdce.folha AS certidaofolha, pdce.livro AS certidaolivro, pdce.data_emissao AS certidaoemissao, pdce.idestado AS certidaoestado, pdce.idtipo_certidao AS certidaotipo, pdce.matricula_certidao AS certidaomatricula, 
	CASE
            WHEN pdce.apresentado = '1'::"char" THEN true
            ELSE false
        END AS blcertidaocomprovada, 
        CASE
            WHEN pdce.img_documento_frente IS NULL THEN false
            ELSE true
        END AS blcertidaodigitalizada, pdce.img_documento_frente AS pathcertidaofrente, pdce.img_documento_verso AS pathcertidaoverso, pdcc.numero AS cpf, 
        CASE
            WHEN pdcc.apresentado = '1'::"char" THEN true
            ELSE false
        END AS blcpfcomprovado, 
        CASE
            WHEN pdcc.img_documento_frente IS NULL THEN false
            ELSE true
        END AS blcpfdigitalizado, pdn.numero AS nis, 
        CASE
            WHEN pdn.apresentado = '1'::"char" THEN true
            ELSE false
        END AS blniscomprovado, 
        CASE
            WHEN pdn.img_documento_frente IS NULL THEN false
            ELSE true
        END AS blnisdigitalizado, pdcc.img_documento_frente AS pathcpffrente, pdcc.img_documento_verso AS pathcpfverso, pdn.img_documento_frente AS pathnisfrente, pdn.img_documento_verso AS pathnisverso, a.inep, a.blnecessidadesespeciais, p.idraca AS cor, a.dtingresso, a.blalunotransferencia, a.tpescolaorigem, a.nomeescolaorigem, a.escolaorigemoutros, p.beneficiario_bolsa_familia AS blbolsafamilia, a.responsavelmatricula, p.fone_residencial AS telresidencial, p.fone_celular AS telcelular, p.fone_recado AS telrecado, p.email, 
        CASE
            WHEN pec.idtipo_endereco IS NULL THEN false
            ELSE true
        END AS blenderecocorrespondencia, a.nomeescolaanterior, a.codigoescolaanterior, a.bairroescolaanterior, a.tipoescolaanterior, a.ultimaescolaridadealuno, a.blaprovadoultimaescolaridade, a.turmaanterior, a.turnoanterior, a.blcompravoescolaridadeanterior, a.blparticipouhorariointegral, a.turnopreferencia, a.bldesejahorariointegral, a.blalunopasseescolar, a.blresponsavelpasseescolar, a."blPaiTemDocumentos", a."nomePai", a."blMaeTemDocumentos", p.nome_mae AS "nomeMae", a."blResponsavelTemDocumentos", a."nomeResponsavel", a."dadoCenso", a."grauParentescoResponsavel", a.blescolaanteriornomunicipio, a.escolaanterioruf, a.escolaanteriorcidade, p.idnacionalidade AS nacionalidadealuno, a.idpessoa, a.escolamunicipal,bltpsanguecomprovado,bltpsanguedigitalizado,tpsanguineo,pathtpsanguefrente,pathtpsangueverso
   FROM educacao."mat_alunoEspecifico" a
   JOIN cabcomum.pessoa p ON a.idpessoa = p.id
   LEFT JOIN cabcomum.pessoa_documento pdc ON p.id = pdc.idpessoa AND pdc.idtipo_documento = 6
   LEFT JOIN cabcomum.pessoa_documento pdce ON p.id = pdce.idpessoa AND pdce.idtipo_documento = 9
   LEFT JOIN cabcomum.pessoa_documento pdcc ON p.id = pdcc.idpessoa AND pdcc.idtipo_documento = 5
   LEFT JOIN cabcomum.pessoa_documento pdn ON p.id = pdn.idpessoa AND pdn.idtipo_documento = 4
   LEFT JOIN cabcomum.pessoa_endereco pec ON p.id = pec.idpessoa AND pec.idtipo_endereco = 2;

ALTER TABLE educacao.mat_aluno OWNER TO postgres;

ALTER TABLE educacao."mat_alunoEspecifico"
   ADD COLUMN observacoes text;

ALTER TABLE educacao."mat_alunoEspecificoTmp"
   ADD COLUMN observacoes text;


----------------------------------------------------------------------------------
--
-- 23/07/2010
--
----------------------------------------------------------------------------------

CREATE OR REPLACE VIEW educacao.mat_aluno AS 
 SELECT a.codaluno, a.blprimeiramatricula, a.dtdeferido, p.nome AS nomealuno, a.pathfotoaluno, pdc.numero AS cadsus, p.idsexo AS sexo, p.data_nascimento AS dtnascimento, p.idestado_nascimento AS ufnascimento, p.idmunicipio_nascimento AS municipionascimento, p.idpais_nascimento AS paisorigemaluno, pdce.cartorio AS certidaocartorio, pdce.termo AS certidaotermo, pdce.folha AS certidaofolha, pdce.livro AS certidaolivro, pdce.data_emissao AS certidaoemissao, pdce.idestado AS certidaoestado, pdce.idtipo_certidao AS certidaotipo, pdce.matricula_certidao AS certidaomatricula, 
        CASE
            WHEN pdce.apresentado = '1'::"char" THEN true
            ELSE false
        END AS blcertidaocomprovada, 
        CASE
            WHEN pdce.img_documento_frente IS NULL THEN false
            ELSE true
        END AS blcertidaodigitalizada, pdce.img_documento_frente AS pathcertidaofrente, pdce.img_documento_verso AS pathcertidaoverso, pdcc.numero AS cpf, 
        CASE
            WHEN pdcc.apresentado = '1'::"char" THEN true
            ELSE false
        END AS blcpfcomprovado, 
        CASE
            WHEN pdcc.img_documento_frente IS NULL THEN false
            ELSE true
        END AS blcpfdigitalizado, pdn.numero AS nis, 
        CASE
            WHEN pdn.apresentado = '1'::"char" THEN true
            ELSE false
        END AS blniscomprovado, 
        CASE
            WHEN pdn.img_documento_frente IS NULL THEN false
            ELSE true
        END AS blnisdigitalizado, pdcc.img_documento_frente AS pathcpffrente, pdcc.img_documento_verso AS pathcpfverso, pdn.img_documento_frente AS pathnisfrente, pdn.img_documento_verso AS pathnisverso, a.inep, a.blnecessidadesespeciais, p.idraca AS cor, a.dtingresso, a.blalunotransferencia, a.tpescolaorigem, a.nomeescolaorigem, a.escolaorigemoutros, p.beneficiario_bolsa_familia AS blbolsafamilia, a.responsavelmatricula, p.fone_residencial AS telresidencial, p.fone_celular AS telcelular, p.fone_recado AS telrecado, p.email, 
        CASE
            WHEN pec.idtipo_endereco IS NULL THEN false
            ELSE true
        END AS blenderecocorrespondencia, a.nomeescolaanterior, a.codigoescolaanterior, a.bairroescolaanterior, a.tipoescolaanterior, a.ultimaescolaridadealuno, a.blaprovadoultimaescolaridade, a.turmaanterior, a.turnoanterior, a.blcompravoescolaridadeanterior, a.blparticipouhorariointegral, a.turnopreferencia, a.bldesejahorariointegral, a.blalunopasseescolar, a.blresponsavelpasseescolar, a."blPaiTemDocumentos", a."nomePai", a."blMaeTemDocumentos", p.nome_mae AS "nomeMae", a."blResponsavelTemDocumentos", a."nomeResponsavel", a."dadoCenso", a."grauParentescoResponsavel", a.blescolaanteriornomunicipio, a.escolaanterioruf, a.escolaanteriorcidade, p.idnacionalidade AS nacionalidadealuno, a.idpessoa, a.escolamunicipal, a.bltpsanguecomprovado, a.bltpsanguedigitalizado, a.tpsanguineo, a.pathtpsanguefrente, a.pathtpsangueverso, a.observacoes 
   FROM educacao."mat_alunoEspecifico" a
   JOIN cabcomum.pessoa p ON a.idpessoa = p.id
   LEFT JOIN cabcomum.pessoa_documento pdc ON p.id = pdc.idpessoa AND pdc.idtipo_documento = 6
   LEFT JOIN cabcomum.pessoa_documento pdce ON p.id = pdce.idpessoa AND pdce.idtipo_documento = 9
   LEFT JOIN cabcomum.pessoa_documento pdcc ON p.id = pdcc.idpessoa AND pdcc.idtipo_documento = 5
   LEFT JOIN cabcomum.pessoa_documento pdn ON p.id = pdn.idpessoa AND pdn.idtipo_documento = 4
   LEFT JOIN cabcomum.pessoa_endereco pec ON p.id = pec.idpessoa AND pec.idtipo_endereco = 2;

ALTER TABLE educacao.mat_aluno OWNER TO postgres;

----------

INSERT INTO educacao.mat_metodoavaliacao(idmetodo, descricaometodo) VALUES (3, 'I, R, B e MB');


----------------------------------------------------------------------------------
--
-- 27/07/2010
--
----------------------------------------------------------------------------------

ALTER TABLE educacao."mat_alunoEspecifico"
   ADD COLUMN excluido boolean NOT NULL DEFAULT false;

ALTER TABLE educacao."mat_alunoEspecificoTmp"
   ADD COLUMN excluido boolean NOT NULL DEFAULT false;

----------------------------------------------------------------------------------
--
-- 05/08/2010
--
----------------------------------------------------------------------------------

ALTER TABLE educacao.mat_professor
   ADD COLUMN desviado boolean NOT NULL DEFAULT false;


----------------------------------------------------------------------------------
--
-- 09/08/2010
--
----------------------------------------------------------------------------------


CREATE OR REPLACE VIEW educacao.mat_aluno AS 
 SELECT a.codaluno, a.blprimeiramatricula, a.dtdeferido, p.nome AS nomealuno, a.pathfotoaluno, pdc.numero AS cadsus, p.idsexo AS sexo, p.data_nascimento AS dtnascimento, p.idestado_nascimento AS ufnascimento, p.idmunicipio_nascimento AS municipionascimento, p.idpais_nascimento AS paisorigemaluno, pdce.cartorio AS certidaocartorio, pdce.termo AS certidaotermo, pdce.folha AS certidaofolha, pdce.livro AS certidaolivro, pdce.data_emissao AS certidaoemissao, pdce.idestado AS certidaoestado, pdce.idtipo_certidao AS certidaotipo, pdce.matricula_certidao AS certidaomatricula, 
        CASE
            WHEN pdce.apresentado = '1'::"char" THEN true
            ELSE false
        END AS blcertidaocomprovada, 
        CASE
            WHEN pdce.img_documento_frente IS NULL THEN false
            ELSE true
        END AS blcertidaodigitalizada, pdce.img_documento_frente AS pathcertidaofrente, pdce.img_documento_verso AS pathcertidaoverso, pdcc.numero AS cpf, 
        CASE
            WHEN pdcc.apresentado = '1'::"char" THEN true
            ELSE false
        END AS blcpfcomprovado, 
        CASE
            WHEN pdcc.img_documento_frente IS NULL THEN false
            ELSE true
        END AS blcpfdigitalizado, pdn.numero AS nis, 
        CASE
            WHEN pdn.apresentado = '1'::"char" THEN true
            ELSE false
        END AS blniscomprovado, 
        CASE
            WHEN pdn.img_documento_frente IS NULL THEN false
            ELSE true
        END AS blnisdigitalizado, pdcc.img_documento_frente AS pathcpffrente, pdcc.img_documento_verso AS pathcpfverso, pdn.img_documento_frente AS pathnisfrente, pdn.img_documento_verso AS pathnisverso, a.inep, a.blnecessidadesespeciais, p.idraca AS cor, a.dtingresso, a.blalunotransferencia, a.tpescolaorigem, a.nomeescolaorigem, a.escolaorigemoutros, p.beneficiario_bolsa_familia AS blbolsafamilia, a.responsavelmatricula, p.fone_residencial AS telresidencial, p.fone_celular AS telcelular, p.fone_recado AS telrecado, p.email, 
        CASE
            WHEN pec.idtipo_endereco IS NULL THEN false
            ELSE true
        END AS blenderecocorrespondencia, a.nomeescolaanterior, a.codigoescolaanterior, a.bairroescolaanterior, a.tipoescolaanterior, a.ultimaescolaridadealuno, a.blaprovadoultimaescolaridade, a.turmaanterior, a.turnoanterior, a.blcompravoescolaridadeanterior, a.blparticipouhorariointegral, a.turnopreferencia, a.bldesejahorariointegral, a.blalunopasseescolar, a.blresponsavelpasseescolar, a."blPaiTemDocumentos", a."nomePai", a."blMaeTemDocumentos", p.nome_mae AS "nomeMae", a."blResponsavelTemDocumentos", a."nomeResponsavel", a."dadoCenso", a."grauParentescoResponsavel", a.blescolaanteriornomunicipio, a.escolaanterioruf, a.escolaanteriorcidade, p.idnacionalidade AS nacionalidadealuno, a.idpessoa, a.escolamunicipal, a.bltpsanguecomprovado, a.bltpsanguedigitalizado, a.tpsanguineo, a.pathtpsanguefrente, a.pathtpsangueverso, a.observacoes, a.excluido
   FROM educacao."mat_alunoEspecifico" a
   JOIN cabcomum.pessoa p ON a.idpessoa = p.id
   LEFT JOIN cabcomum.pessoa_documento pdc ON p.id = pdc.idpessoa AND pdc.idtipo_documento = 6
   LEFT JOIN cabcomum.pessoa_documento pdce ON p.id = pdce.idpessoa AND pdce.idtipo_documento = 9
   LEFT JOIN cabcomum.pessoa_documento pdcc ON p.id = pdcc.idpessoa AND pdcc.idtipo_documento = 5
   LEFT JOIN cabcomum.pessoa_documento pdn ON p.id = pdn.idpessoa AND pdn.idtipo_documento = 4
   LEFT JOIN cabcomum.pessoa_endereco pec ON p.id = pec.idpessoa AND pec.idtipo_endereco = 2;

ALTER TABLE educacao.mat_aluno OWNER TO postgres;


----------------------------------------------------------------------------------
--
-- 10/08/2010
--
----------------------------------------------------------------------------------

ALTER TABLE educacao.mat_documentos
   ADD COLUMN pathdiploma character varying(256);
   
   
----------------------------------------------------------------------------------
--
-- 20/08/2010
--
----------------------------------------------------------------------------------

-- Alteração na visão educacao.estatisticacadalunodistribuicaocor

CREATE OR REPLACE VIEW educacao.estatisticacadalunodistribuicaocor AS 
 SELECT c.descricaocorraca, count(*) AS qtd
   FROM educacao.mat_aluno a
   JOIN educacao.corracaaluno c ON a.cor = c.corraca
  GROUP BY c.corraca, c.descricaocorraca, a.cor
  ORDER BY c.corraca;

ALTER TABLE educacao.estatisticacadalunodistribuicaocor OWNER TO postgres;
   
   
----------------------------------------------------------------------------------
--
-- 23/08/2010
--
----------------------------------------------------------------------------------
   
ALTER TABLE educacao.mat_professor
   ADD COLUMN nummemoprofessor integer;

----------------------------------------------------------------------------------
--
-- 27/08/2010
--
----------------------------------------------------------------------------------

ALTER TABLE educacao.mat_professor
   ADD COLUMN excluido boolean NOT NULL DEFAULT false;
