minhagraduacao_querys.sql

----------------------------------------------------------------------
-- Ler arquivos do INEP
----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION inep_data.func_file_read_format_save(param_path_file varchar, type varchar) RETURNS void AS 
$$
DECLARE
	code_load VARCHAR := md5(random()::text);
BEGIN
	EXECUTE format('copy inep_data.temp_file(line) FROM ''%s''', param_path_file);
	IF type = 'institution' THEN 
		INSERT INTO 
			inep_data.institutions(  co_ies
						,no_ies
						,co_mantenedora
						,co_categoria_administrativa
						,no_categoria_administrativa
						,co_organizacao_academica
						,no_organizacao_academica
						,co_municipio_ies
						,no_municipio_ies
						,co_uf_ies
						,sgl_uf_ies
						,no_regiao_ies
						,in_capital_ies
						,qt_tec_total
						,qt_tec_fund_incomp_masc
						,qt_tec_fund_incomp_fem
						,qt_tec_fund_comp_masc
						,qt_tec_fund_comp_fem
						,qt_tec_medio_masc
						,qt_tec_medio_fem
						,qt_tec_superior_masc
						,qt_tec_superior_fem
						,qt_tec_especializacao_masc
						,qt_tec_especializacao_fem
						,qt_tec_mestrado_masc
						,qt_tec_mestrado_fem
						,qt_tec_doutorado_masc
						,qt_tec_doutorado_fem
						,in_acesso_portal_capes
						,in_acesso_outras_bases
						,in_referente
						,vl_receita_propria
						,vl_transferencia
						,vl_outra_receita
						,vl_des_pessoal_rem_docente
						,vl_des_pessoal_rem_tecnico
						,vl_des_pessoal_encargo
						,vl_des_custeio
						,vl_des_investimento
						,vl_des_pesquisa
						,vl_des_outras
						,created_at
						,updated_at
						,code_load ) 
		SELECT 
			 CAST(SUBSTR(line, 1, 8) AS INTEGER) AS co_ies
			,SUBSTR(line, 9, 200) AS no_ies
			,CAST(SUBSTR(line, 209, 8) AS INTEGER) AS co_mantenedora
			,CAST(SUBSTR(line, 217, 8) AS INTEGER) AS co_categoria_administrativa
			,SUBSTR(line, 225, 100) AS no_categoria_administrativa
			,CAST(SUBSTR(line, 325, 8) AS INTEGER) AS co_organizacao_academica
			,SUBSTR(line, 333, 100) AS no_organizacao_academica
			,CAST(SUBSTR(line, 433, 8) AS INTEGER) AS co_municipio_ies
			,SUBSTR(line, 441, 150) AS no_municipio_ies
			,CAST(SUBSTR(line, 591, 8) AS INTEGER) AS co_uf_ies
			,SUBSTR(line, 599, 2) AS sgl_uf_ies
			,SUBSTR(line, 601, 30) AS no_regiao_ies
			,CAST(SUBSTR(line, 631, 8) AS INTEGER) AS in_capital_ies
			,CAST(SUBSTR(line, 639, 8) AS INTEGER) AS qt_tec_total
			,CAST(SUBSTR(line, 647, 8) AS INTEGER) AS qt_tec_fund_incomp_masc
			,CAST(SUBSTR(line, 655, 8) AS INTEGER) AS qt_tec_fund_incomp_fem
			,CAST(SUBSTR(line, 663, 8) AS INTEGER) AS qt_tec_fund_comp_masc
			,CAST(SUBSTR(line, 671, 8) AS INTEGER) AS qt_tec_fund_comp_fem
			,CAST(SUBSTR(line, 679, 8) AS INTEGER) AS qt_tec_medio_masc
			,CAST(SUBSTR(line, 687, 8) AS INTEGER) AS qt_tec_medio_fem
			,CAST(SUBSTR(line, 695, 8) AS INTEGER) AS qt_tec_superior_masc
			,CAST(SUBSTR(line, 703, 8) AS INTEGER) AS qt_tec_superior_fem
			,CAST(SUBSTR(line, 711, 8) AS INTEGER) AS qt_tec_especializacao_masc
			,CAST(SUBSTR(line, 719, 8) AS INTEGER) AS qt_tec_especializacao_fem
			,CAST(SUBSTR(line, 727, 8) AS INTEGER) AS qt_tec_mestrado_masc
			,CAST(SUBSTR(line, 735, 8) AS INTEGER) AS qt_tec_mestrado_fem
			,CAST(SUBSTR(line, 743, 8) AS INTEGER) AS qt_tec_doutorado_masc
			,CAST(SUBSTR(line, 751, 8) AS INTEGER) AS qt_tec_doutorado_fem
			,CAST(SUBSTR(line, 759, 8) AS INTEGER) AS in_acesso_portal_capes
			,CAST(SUBSTR(line, 767, 8) AS INTEGER) AS in_acesso_outras_bases
			,CAST(SUBSTR(line, 775, 8) AS INTEGER) AS in_referente
			,CAST(SUBSTR(line, 783, 14) AS FLOAT) AS vl_receita_propria
			,CAST(SUBSTR(line, 797, 14) AS FLOAT) AS vl_transferencia
			,CAST(SUBSTR(line, 811, 14) AS FLOAT) AS vl_outra_receita
			,CAST(SUBSTR(line, 825, 14) AS FLOAT) AS vl_des_pessoal_rem_docente
			,CAST(SUBSTR(line, 839, 14) AS FLOAT) AS vl_des_pessoal_rem_tecnico
			,CAST(SUBSTR(line, 853, 14) AS FLOAT) AS vl_des_pessoal_encargo
			,CAST(SUBSTR(line, 867, 14) AS FLOAT) AS vl_des_custeio
			,CAST(SUBSTR(line, 881, 14) AS FLOAT) AS vl_des_investimento
			,CAST(SUBSTR(line, 895, 14) AS FLOAT) AS vl_des_pesquisa
			,CAST(SUBSTR(line, 909, 14) AS FLOAT) AS vl_des_outras 
			,NOW()
			,NOW()
			,code_load
		FROM inep_data.temp_file;
	END IF;

 	DELETE FROM inep_data.temp_file;
END;
$$ 
LANGUAGE plpgsql;

----------------------------------------------------------------------
-- Importação das cidades
----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION pgloader.func_import_cities_schools()
  RETURNS void AS
$BODY$
DECLARE
	contador      int;
	v_uf_id       int;
	v_city_id     int;
	v_loop_result RECORD;
BEGIN
	contador = 1;	
	FOR v_loop_result IN (SELECT no_regiao,fk_cod_estado,sigla,pk_cod_municipio,no_municipio,pk_cod_distrito,no_distrito FROM pgloader.ts_cidade)
	LOOP
		-- Recupera o ID da UF
		SELECT id INTO v_uf_id FROM public.ufs WHERE co_uf = v_loop_result.fk_cod_estado; 
			
		-- Caso o registro nao exista, adiciona um novo
		IF NOT EXISTS (SELECT id FROM public.cities WHERE co_municipio = v_loop_result.pk_cod_municipio) THEN	
			INSERT INTO public.cities (
				 uf_id
				,co_municipio
				,name
				,created_at
				,updated_at
			) VALUES (
				 v_uf_id
				,v_loop_result.pk_cod_municipio
				,public.func_humanize_text(v_loop_result.no_municipio) 
				,now()
				,now()
			);			
		END IF;

	END LOOP;
END;	
$BODY$
  LANGUAGE plpgsql;
  
----------------------------------------------------------------------
-- Importação das Escolas
----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION pgloader.func_import_schools()
  RETURNS void AS
$BODY$
DECLARE
	v_loop_result RECORD;
BEGIN
	FOR v_loop_result IN (SELECT  ts_escola.cod_entidade
				      ,ts_escola.no_entidade
				      ,ts_escola.cod_orgao_regional_inep
				      ,ts_escola.cod_estado
				      ,ts_escola.sigla
				      ,ts_escola.cod_municipio
				      ,ts_escola.cod_distrito
				      ,ts_escola.id_dependencia_adm
				      ,ts_escola.desc_categoria_escola_privada
				      ,ts_escola.id_tipo_convenio_poder_publico
				      ,ufs.id as uf_id
				      ,cities.id as city_id
				FROM pgloader.ts_escola ts_escola  
				JOIN public.ufs ufs ON ufs.co_uf = ts_escola.cod_estado
				JOIN public.cities cities ON cities.co_municipio = ts_escola.cod_municipio)
	LOOP

		-- Caso o registro nao exista, adiciona um novo
		IF NOT EXISTS (SELECT id FROM public.schools WHERE cod_entidade = v_loop_result.cod_entidade) THEN	
			INSERT INTO public.schools (
				 uf_id
				,city_id
				,cod_entidade
				,no_entidade
				,cod_orgao_regional_inep
				,dependencia_adm
				,created_at
				,updated_at
			) VALUES (
				 v_loop_result.uf_id
				,v_loop_result.city_id
				,v_loop_result.cod_entidade
				,public.func_humanize_text(v_loop_result.no_entidade) 
				,v_loop_result.cod_orgao_regional_inep 
				,v_loop_result.id_dependencia_adm 
				,now()
				,now()
			);			
		END IF;

	END LOOP;
END;	
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
----------------------------------------------------------------------
-- Importação das Graduações
----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION pgloader.func_import_graduations()
  RETURNS void AS
$BODY$
DECLARE
	contador             int;
	v_uf_id              int;
	v_city_id            int;
	v_institution_id     int;
	v_graduation_id      int;
	v_academic_degree_id int;
	v_method_teaching_id int;
	v_loop_result        RECORD;
	v_new_load_code      VARCHAR := md5(random()::text);
	v_new_old_code       VARCHAR := md5(random()::text);
BEGIN
	-- Atualiza todos os codigos de carga
	UPDATE public.graduations SET load_code = v_new_old_code;
	
	contador = 1;	
	FOR v_loop_result IN (SELECT  coalesce(ds_grau_academico, '') as ds_grau_academico 
	                             ,coalesce(ds_modalidade_ensino, '') as ds_modalidade_ensino 
	                             ,co_curso
	                             ,no_curso
	                             ,co_municipio_curso
	                             ,co_uf_curso
	                             ,co_ies
	                             ,co_ies
	                      FROM pgloader.graduations)
	LOOP

		/*------------------------------------------------------------------------------------
		 *  GRAU ACADEMICO - ADICIONA UM NOVO CASO NAO EXISTA
		 */-----------------------------------------------------------------------------------
		IF NOT EXISTS (SELECT id FROM public.academic_degrees WHERE name = v_loop_result.ds_grau_academico) THEN

			-- Insere uma nova graduacao
			INSERT INTO  public.academic_degrees(  
				 name
				,created_at
				,updated_at
			) VALUES (
				 v_loop_result.ds_grau_academico
				,now()
				,now()
			);

		END IF;

		/*------------------------------------------------------------------------------------
		 *  MODALIDADE DE ENSINO - ADICIONA UM NOVO CASO NAO EXISTA
		 */-----------------------------------------------------------------------------------
		IF NOT EXISTS (SELECT id FROM public.method_teachings WHERE name = v_loop_result.ds_modalidade_ensino) THEN

			-- Insere uma nova graduacao
			INSERT INTO  public.method_teachings(  
				 name
				,created_at
				,updated_at
			) VALUES (
				 v_loop_result.ds_modalidade_ensino
				,now()
				,now()
			);

		END IF;

		/*------------------------------------------------------------------------------------
		 *  GRADUACAO - ADICIONA UM NOVO CASO NAO EXISTA
		 */-----------------------------------------------------------------------------------		
		IF NOT EXISTS (SELECT id FROM public.graduations WHERE no_curso = v_loop_result.no_curso) THEN

			INSERT INTO  public.graduations(  
				 no_curso
				,active
				,load_code
				,created_at
				,updated_at
			) VALUES (
				 v_loop_result.no_curso
				,false
				,v_new_load_code
				,now()
				,now()
			);

		END IF;

		-- Recupera o ID do grau academico
		SELECT id INTO v_academic_degree_id FROM public.academic_degrees WHERE name = v_loop_result.ds_grau_academico; 
		
		-- Recupera o ID do metodo de ensinio
		SELECT id INTO v_method_teaching_id FROM public.method_teachings WHERE name = v_loop_result.ds_modalidade_ensino; 
		
		/*------------------------------------------------------------------------------------
		 *  LIGACAO ENTRE INSTITUICAO E GRADUACAO - ADICIONA UM NOVO CASO NAO EXISTA
		 */-----------------------------------------------------------------------------------
		IF NOT EXISTS (SELECT id FROM public.graduations_institutions WHERE co_curso = v_loop_result.co_curso) THEN
		
			-- Recupera o ID da IES
			IF v_loop_result.co_municipio_curso != 0 THEN
				-- Recupera o ID da UFS
				SELECT id INTO v_uf_id FROM public.ufs WHERE co_uf = v_loop_result.co_uf_curso; 
				SELECT id INTO v_institution_id FROM public.institutions WHERE co_ies = v_loop_result.co_ies AND uf_id = v_uf_id; 
			ELSE
				SELECT id INTO v_institution_id FROM public.institutions WHERE co_ies = v_loop_result.co_ies; 
			END IF;
			
			-- Recupera o ID da graduacao
			SELECT id INTO v_graduation_id FROM public.graduations WHERE no_curso = v_loop_result.no_curso; 

			-- Novo relacionamento IES e Graduacao	
			INSERT INTO public.graduations_institutions (
				 co_curso
				,institution_id
				,graduation_id
				,academic_degree_id
				,method_teaching_id
			) VALUES (
				 v_loop_result.co_curso
				,v_institution_id
				,v_graduation_id
				,v_academic_degree_id
				,v_method_teaching_id
			);
		ELSE

			UPDATE public.graduations_institutions
			SET academic_degree_id = v_academic_degree_id,
			    method_teaching_id = v_method_teaching_id
			WHERE co_curso = v_loop_result.co_curso;
			
		END IF;

		contador = contador + 1;	
	END LOOP;
END;	
$BODY$
  LANGUAGE plpgsql VOLATILE;