In noaa-garfo/discaRd: Cochran bycatch estimation

knitr::opts_chunk$set(echo=FALSE
                                            , warning = FALSE
                                            , message = FALSE
                                            , cache = FALSE
                                            , progress = TRUE
                                            , verbose = FALSE
                                            , comment = F
                                            , error = FALSE
                                            , dev = 'png'
                                            , dpi = 200
                                            , prompt = F
                                            , results='hide')

options(dplyr.summarise.inform = FALSE)

# library(odbc)
library(ROracle)
library(keyring)
library(apsdFuns)
library(dplyr, warn.conflicts = FALSE)
# library(dbplyr)
library(ggplot2)
# library(config)
library(stringr)
library(discaRd)
library(fst)
options(scipen = 999)

# unlock keyring
keyring::keyring_unlock("apsd_ma")

# local run
# dw_apsd <- config::get(value = "apsd", file = "K:/R_DEV/config.yml")

# if on server..
# dw_apsd <- config::get(value = "maps", file = "~/config.yml")


# connect to MAPS

con_maps = apsdFuns::roracle_login(key_name = 'apsd_ma', key_service = 'maps')

# 
# con_maps <- ROracle::dbConnect(
#     drv = ROracle::Oracle(),
#     username = dw_apsd$uid,
#     password = dw_apsd$pwd,
#     dbname = "NERO.world"
# )

'%!in%' <- function(x,y)!('%in%'(x,y))

source('~/PROJECTS/discaRd/CAMS/R/cams_discard_functions.R')

setwd('~/PROJECTS/discaRd/CAMS/MODULES/GROUNDFISH/')

    sq = readr::read_lines("~/PROJECTS/discaRd/CAMS/SQL/match_discard_oblen_example.sql") %>% 
        glue_collapse(sep = "\n") %>% 
        glue_sql(.con = con) %>% 
        gsub(x = ., pattern = '&YEAR', replacement = y)

oblen = ROracle::dbGetQuery(con_maps, sq)
oblen = as_tibble(oblen)

library(DT)

oblen %>% 
    # group_by(COMMON_NAME
    #                , SPECIES_STOCK) %>% 
    # dplyr::summarise(nvtr = n_distinct(VTRSERNO)
    #                                , KALL = sum(SUBTRIP_KALL, na.rm = T)
    #                                , DISCARD = round(sum(CAMS_DISCARD, na.rm = T))) %>% 
    DT::datatable(caption = 'OBLEN data joined from cams_link3_subtrip and discard_all_years')
    # DT::formatRound(c('nvtr','KALL','DISCARD'), digits = 0, interval = 3)

oblen %>% 
    filter(!is.na(NEGEAR)) %>% 
    # group_by(YEAR) %>% 
    ggplot()+
    geom_density(aes(LENANML))+
    facet_wrap(~NEGEAR)

oblen %>% 
    filter(!is.na(NEGEAR)) %>% 
    ggplot()+
    geom_point(aes(SAMPWEIGHT, LENANML ))+
    geom_smooth(aes(SAMPWEIGHT, LENANML ))+
    facet_wrap(~NEGEAR)

# oblen = tbl(con_maps, sql(sq))


 ob_cams =  oblen %>% 
    left_join(., y = tbl(con_maps, sql("
        select * from cams_discard_all_years
      where GF = 1
    and Year = 2020
    and species_itis = 172873"
      )
      ), by = 'CAMS_SUBTRIP', copy = T)

ob_cams %>% 
    filter(!is.na(CAMS_GEAR_GROUP)) %>% 
    ggplot()+
    geom_point(aes(SAMPWEIGHT, LENANML ))+
    geom_smooth(aes(SAMPWEIGHT, LENANML ))+
    facet_grid(MESHGROUP~CAMS_GEAR_GROUP)


# cams_obs = tbl(con_maps, sql("select * from cams_obs_catch"))