Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.