Extrair_dados_Porta_transparencia

mudou_estrutura <- FALSE
tipo_nova_estrutura <- c(character(1))

mes_fim<-12

for (ano in 2013:2018){
  
  if (ano == 2018) {mes_fim<-ult_mes_disp}
  ifelse(ano==2012, mes_ini <-12, mes_ini<-1)
  
  for (mes in mes_ini:mes_fim){
    
    mes<- ifelse(mes>=10,as.character(mes),paste0('0',mes))
    data<-as.character(LastDayInMonth(paste0(ano,'-',mes,'-01')),format='%Y%m%d')
    for (tipo in c('M','C')){
      
      print(ano)
      print(mes)
      print(tipo)
      
      
      arq_cadastro <- ifelse(tipo=='C', paste0(data,'_Cadastro.csv'), paste0(data,'_CadastroMilitares.csv'))
      arq_remunera <- ifelse(tipo=='C', paste0(data,'_Remuneracao.csv'),paste0(data,'_RemuneracaoMilitares.csv') ) 
      
      
      if (!mudou_estrutura || (mudou_estrutura && !(tipo %in% tipo_nova_estrutura ))){
        cc <- rep('NULL', 50)       ## skip all 39 columns
        cc[c(1, 5, 10, 11, 12,18,20,23,25)] <- NA     
        
        df_cadastro<- read.csv2(arq_cadastro,sep = "\t",header = TRUE, colClasses=cc,  stringsAsFactors = FALSE, na.strings = "",quote = "")
        
        #Em algum momento a ordem dos campos se altera
        if (names(df_cadastro)[6]!='ORG_LOTACAO'){
          print('houve mudança de estrutura')
          mudou_estrutura <- TRUE
          tipo_nova_estrutura<-c(tipo_nova_estrutura,tipo)

        } 
        
        
      }
      
      if (mudou_estrutura & tipo %in% tipo_nova_estrutura){
        cc <- rep('NULL', 50)       ## skip all 39 columns
        cc[c(1, 5, 10, 11, 12,19,21,25,27)] <- NA     
        
        df_cadastro<- read.csv2(arq_cadastro,sep = "\t",header = TRUE, colClasses=cc,  stringsAsFactors = FALSE, na.strings = "",quote = "")
        
      }
      
      
      

      cc <- rep('NULL', 50)       ## skip all 36 columns
      
      if ((substr(data,1,6)>="201704") && (tipo=='C')){
        
        cc[c(3,6,8,10,12,14,16,30,36,38)] <- NA     
        #cc[c(3,6,30,36,38)] <- NA 
        
      } else{      
        cc[c(3,6,8,10,12,14,16,28,34,36)] <- NA     
        #cc[c(3,6,28,34,36)] <- NA     
      }
      
      df_remunera<- read.csv2(arq_remunera,sep = "\t",header = TRUE, colClasses=cc,  stringsAsFactors = FALSE, na.strings = "",quote = "")


      df_cadastro$data <- as.character(LastDayInMonth(paste0(ano,'-',mes,'-01')),format='%Y-%m-%d')
      df_remunera$data <- as.character(LastDayInMonth(paste0(ano,'-',mes,'-01')),format='%Y-%m-%d')
      

      if ((substr(data,1,6)>="201702") && (tipo=='C')){ #A partir de 2017/02 tem dados para honorários
        cc <- rep('NULL', 10)       ## skip all 39 columns
        cc[c(3,6)] <- NA     
        
        arq_honorario <- paste0(data,'_HonorariosAdvocaticios.csv')
        df_honorario<- read.csv2(arq_honorario,sep = "\t",header = TRUE, colClasses=cc,  stringsAsFactors = FALSE, na.strings = "",quote = "")
        if (mes=="01"){
          df_honorario$data <- as.character(LastDayInMonth(paste0(ano,'-','12','-01')),format='%Y-%m-%d')
        } else{
          df_honorario$data <- as.character(LastDayInMonth(paste0(ano,'-',(as.numeric(mes)-1),'-01')),format='%Y-%m-%d')
        }
        
        names(df_honorario)[2] <- "honorarios"
        
        df_base_plus<-merge(df_cadastro,df_remunera, by.x = "Id_SERVIDOR_PORTAL", by.y = "ID_SERVIDOR_PORTAL")
        df_base_plus<-merge(df_base_plus,df_honorario, by.x = "Id_SERVIDOR_PORTAL", by.y = "ID_SERVIDOR_PORTAL",all.x = TRUE)
        df_base_plus<- df_base_plus[,c(1:19,21)]
        
      } else{
        df_base_plus<-merge(df_cadastro,df_remunera, by.x = "Id_SERVIDOR_PORTAL", by.y = "ID_SERVIDOR_PORTAL")
        df_base_plus$honorarios<-0
        df_base_plus<-df_base_plus[,-20]

      }
      
            
      
      
      rm(list=c("df_remunera"))
      
      df_funcao<- df_base_plus[which(!is.na(df_base_plus$NIVEL_FUNCAO)),]
      
      df_servidor<- df_base_plus[which(is.na(df_base_plus$NIVEL_FUNCAO)),]
      
      df_cargo_funcao<-merge(df_servidor,df_funcao,by.x = "Id_SERVIDOR_PORTAL", by.y = "Id_SERVIDOR_PORTAL" )
      
      rm(list = c("df_funcao","df_servidor"))
      
      df_base_plus<-merge(df_base_plus,df_cargo_funcao[,1:2],by.x = "Id_SERVIDOR_PORTAL", by.y = "Id_SERVIDOR_PORTAL" ,all.x = TRUE)
      
      df_base_plus$tem_funcao <- FALSE
      
      df_base_plus$tem_funcao[which(is.na(df_base_plus$DESCRICAO_CARGO))]<-TRUE
      df_base_plus$DESCRICAO_CARGO[which(is.na(df_base_plus$DESCRICAO_CARGO))]<-df_base_plus$DESCRICAO_CARGO.x[which(is.na(df_base_plus$DESCRICAO_CARGO))]
      
      #cols<-c(6,2,7,8,10,9,13,11)
      cols<- c(10,2,6,7,8,9,11,12,13,14,15,16,17,18,19,20,22)
      
      df_base_plus<-df_base_plus[,cols]
      
      
      df_base_plus$honorarios[which(is.na(df_base_plus$honorarios))]<-0
      
      #names(df_base_plus) <- c("DATA", "DESCRICAO_CARGO","REMUNERACAO_BRUTA","REMUNERACAO_LIQUIDA","TOTAL_JETOM","TOTAL_VERBAS_IDENIZATORIAs","TEM_FUNCAO","HONORARIOS")
      names(df_base_plus) <- c("DATA", 
                          "DESCRICAO_CARGO",
                          "LOTACAO",
                          "SUP_LOTACAO",
                          "EXERCICIO",
                          "SUP_EXERCICIO",
                          "REMUNERACAO_BRUTA",
                          "ABATE_TETO",
                          "GRAT_NATALINA",
                          "ABATE_NATALINA",
                          "FERIAS",
                          "OUTRAS_EVENTUAIS",
                          "REMUNERACAO_LIQUIDA",
                          "TOTAL_VERBAS_IDENIZATORIAS",
                          "TOTAL_JETOM",
                          "HONORARIOS",
                          "TEM_FUNCAO")
      
      
      
      
      
      df_base_plus<- df_base_plus %>% group_by(DATA,DESCRICAO_CARGO,TEM_FUNCAO,LOTACAO,
                                               SUP_LOTACAO,
                                               EXERCICIO,
                                               SUP_EXERCICIO)
      
      
      
      
      df_base_plus<-df_base_plus %>% summarise(
        quantidade = length(DESCRICAO_CARGO),
        rem_bruto = sum(REMUNERACAO_BRUTA),
        rem_liq = sum(REMUNERACAO_LIQUIDA),
        jetom = sum(TOTAL_JETOM),
        verbas = sum(TOTAL_VERBAS_IDENIZATORIAS),
        honorarios =sum(as.numeric(HONORARIOS)),
        abate_teto = sum(ABATE_TETO),
        grat_natalina = sum(GRAT_NATALINA),
        abate_natalina = sum(ABATE_NATALINA),
        ferias = sum(FERIAS),
        outras_eventuais = sum(OUTRAS_EVENTUAIS)
      )
      
      df_base<-rbind(df_base,df_base_plus)

    }
  }
}

write.csv2(df_base,file=paste0("base_",Sys.Date(),".csv"),fileEncoding = "UTF-8")