library(reshape2) #restructures data with melt(),dcast()
library(nrsa)
library(plyr) # data management functions
#library(dplyr) # even better dataframe manipulation functions
library(RODBC) # connects to databases
#library(magrittr) # piping package
#library(rChoiceDialogs) # enables GUI working directory selection

# Restrict number of decimal places reported in output
options(digits=3)

# Set your working directory, calculator will not run without correct file path
#setwd(choose.dir(caption='Select Working Directory'))

# Connect to Access Database and read in Access tables
channel   <- odbcConnectAccess("PHab_03_25_2016.mdb")
reach     <- sqlQuery(channel, "SELECT * FROM Reach ORDER BY SampleID")
thalweg   <- sqlQuery(channel, "SELECT * FROM Thalweg ORDER BY SampleID")
wet       <- sqlQuery(channel, "SELECT * FROM Wet ORDER BY SampleID")
bankfull  <- sqlQuery(channel, "SELECT * FROM BankFull ORDER BY SampleID")
wood      <- sqlQuery(channel, "SELECT * FROM Wood ORDER BY SampleID")
incision  <- sqlQuery(channel, "SELECT * FROM Incision ORDER BY SampleID")
embed     <- sqlQuery(channel, "SELECT * FROM Embeddedness ORDER BY SampleID")
substrate <- sqlQuery(channel, "SELECT * FROM Substrate ORDER BY SampleID")

kVisitIdVars <- c('SampleID','StationID','Date')

## Residual Pool Calculations
# Combine columns from the Reach and Thalweg tables
kThalwegMeasures <- c('ThalwegN','ReachLength','Interval','Slope')
thalweg <- join(thalweg,
                reach[, c(kVisitIdVars, kThalwegMeasures)],
                by = kVisitIdVars)
is.na(thalweg)[,grep('1[0-4]', names(thalweg))] <- thalweg$ThalwegN == 10

mthal <- melt(thalweg,
              id.vars = c(kVisitIdVars, kThalwegMeasures),
              na.rm   = T)
depth.met <- calculateThalwegDepthMetrics(uid         = mthal$SampleID,
                                          is.wadeable = rep(TRUE, nrow(mthal)),
                                          depth       = mthal$value)

# Substrate
lsub <- melt(substrate,
             id.vars       = kVisitIdVars,
             variable.name = 'Location',
             value.name    = 'SubstrateType')

is.na(lsub$SubstrateType) <- lsub$SubstrateType == 'MISSING'
sub.met <- calculateWadeSubstrateMetrics(uid        = lsub$SampleID,
                                         size.class = lsub$SubstrateType)

# Inside Outside calcs
lsub$inout <- grepl('Left Middle|Middle|Right Middle', lsub$Location)
f <- function(x){
  calculateWadeSubstrateMetrics(x$SampleID, x$SubstrateType)
}
sub.met.inout <- ddply(lsub, .(inout), f)

# Embededness
membed <- melt(embed, id.vars = kVisitIdVars)
embed.met <- calculateSubstrateEmbed(uid       = membed$SampleID,
                                     embed     = membed$value,
                                     is.center = grepl('M', membed$variable))