library(tidyverse)
path <- getwd()
isRunningInShiny <- Sys.getenv('SHINY_PORT') != ""
if(endsWith(path,'R') | isRunningInShiny){
source('Schemas/ExpensesSchemas.R')
source('Classes/util.R')
source('Mock/mockExpenses.R')
} else {
source('R/Schemas/ExpensesSchemas.R')
source('R/Classes/util.R')
source('R/Mock/mockExpenses.R')
}
setClass("Expenses", slots =c(transactions = "tbl_df",
summary = "tbl_df",
curDate = "Date"),
prototype=list(
transactions = NULL,
summary = NULL
))
gatherExpenses <- function()
{
initTran <- getTranInit()
clnTran <- transformTran(initTran)
income <- getIncomeInit()
#incomes do not need to be multipled by -1, so pass 1
clnIncome <- transformTran(income,debitCredit = 1)
combiedTran <- rbind(clnTran,clnIncome)
}
#Creator
Expenses = function(curDate) {
dqDs <- gatherExpenses()
sumTran <- getsummaryTran(dqDs)
new("Expenses",transactions=dqDs, summary=sumTran, curDate = curDate)
}
#' Get Income Transaction dataset
#' @return data frame
#' @export
#' @examples
#' getTranInit()
getIncomeInit <- function() {
getGoogleSheetFromSheetName("ravi-income")
#mockIncomeInit()
}
#' Mock for a Transaction sheet
#' @return mocked Transaction Sheet
#' @export
#' @examples
#' mockTranInit()
mockTranInit <- function() {
curDate <- Sys.Date()
#a record less than a week
ltWeek1 <- format(curDate - 5, "%m/%d/%Y")
#a record gt than a week but less than 2 weeks
ltWeek2 <- format(curDate - 9, "%m/%d/%Y")
# a record older than 2 weeks
ltWeekOld <- format(curDate - 15, "%m/%d/%Y")
tr1 = createTran(Date=ltWeek1,
Store = "Wegman's",
ReceiptNo = "1 week",
Item = "Olive Oil",
Amount = "$10.00",
Total = "$10.00",
Reconciled="Yes")
tr2 = createTran(Date=ltWeek1,
Store = "Wegman's",
ReceiptNo = "1 week",
Item = "Organic chicken",
Amount = "$32.00",
Total = "$32.00",
Reconciled = NA)
tr3 = createTran(Date=ltWeek1,
Store = "Wegman's",
Beneficiary = "soundari",
ReceiptNo = "1 week",
Item = "Olive Oil",
Amount = "$10.00",
Total = "$10.00",
Reconciled="Yes")
tr4 = createTran(Date=ltWeek1,
Store = "Wegman's",
ReceiptNo = "1 week",
Beneficiary = "soundari",
Item = "Organic chicken",
Amount = "$32.00",
Total = "$32.00",
Reconciled = NA)
tr5 = createTran(Date=ltWeek2,
Store = "Trader Joe's",
ReceiptNo = "2 week",
Item = "Cheese",
Amount = "$11.10",
Total = "$11.10")
tr6 = createTran(Date=ltWeekOld,
Store = "Trader Joe's",
Item = "Brussels sprouts",
ReceiptNo = "old",
Amount = "$6.8",
Total = "$6.8")
#Errored record
tr7 = createTran(Date=ltWeekOld,
Store = "Trader Joe's",
Item = "Eggplant",
ReceiptNo = "old",
Amount = "$3.2",
Total = "$3.2")
tr8 = createTran(Date=ltWeekOld,
Store = "Trader Joe's",
Item = "Brussels sprouts",
ReceiptNo = "old",
Amount = "$4.8",
Total = "$4.8")
#Join all 3 records
rbind(tr1,tr2,tr3,tr4,tr5,tr6,tr7,tr8)
}
#' Mock for a Transaction sheet
#' @return mocked Transaction Sheet
#' @export
#' @examples
#' mockTranInit()
mockIncomeInit <- function() {
curDate <- Sys.Date()
#a record less than a week
ltWeek1 <- format(curDate - 5, "%m/%d/%Y")
tr1 = createTran(Date=ltWeek1,
Store = "Banana Republic",
Category = "Clothing",
ReceiptNo = "1 week",
Item = "Banana Republic Return",
Amount = "$12.00",
Total = "$12.00",
Reconciled="No")
tr1
}
#' Summarize transactions to get the dollar amount per receipt
#' @param clnTran cleaned Transaction data
#' @return data frame
#' @export
#' @examples
#' initMint <- getGoogleSheet("Ravi Transactions")
#' initTran <- getGoogleSheet("Ravi - mint")
#' tr <- transformTran(initTran)
#' getsummaryTran(tr)
getsummaryTran <- function(clnTran) {
#Make sure our schema is cln schema
checkSchema(clnTran,clnTranSchema)
sumDs <- group_by(clnTran,clnDate,ReceiptNo,Store) %>%
summarise(smAmount=sum(trTotal),
Reconciled=checkReconciled(Reconciled)) %>%
ungroup() %>%
mutate(TransactionDate =format(clnDate, "%m/%d/%Y")) %>%
select(ReceiptNo,TransactionDate,Store,smAmount,Reconciled) %>%
arrange(desc(TransactionDate))
#make sure schema is correct
checkSchema(sumDs,summaryTranSchema)
sumDs
}
#' Transform the transaction dataset
#' @param x dataset from getGoogleSheet
#' @param weeks no of weeks to retrieve the data for
#' @return data frame
#' @export
#' @examples
#' init <- getGoogleSheet("Ravi Transactions")
#' transformTran(init)
transformTran <- function(ds,weeks="ALL",debitCredit = -1) {
checkSchema(ds,initTranSchema)
if(weeks == "ALL")
weekData <- ds
else{
numWeeks <- as.numeric(weeks)
weekData <- filter( ds,difftime(curDate,
as.Date(Date,"%m/%d/%Y"),
units="weeks") <= numWeeks )
}
#clean existing columns
clnDs <- mutate(weekData,
clnTotal = clnDollar(Total),
clnDate=as.Date(Date,"%m/%d/%Y"))
#transformations
#1. format date
#2. multiply -1 since the transactions are debit
trDs <- mutate( clnDs,
trYearMonth=format(clnDate, "%y-%b"),
trDateStr=format(clnDate, "%m/%d/%Y"),
trTotal = clnTotal * debitCredit)
#Remove unnecessary columns to bring to target Dataset
outDs <- select(trDs,-Total,-Date)
#Make sure our schema is cln schema
checkSchema(outDs,clnTranSchema)
outDs
}
#' Get Init Transaction dataset
#' @return data frame
#' @export
#' @examples
#' getTranInit()
getTranInit <- function() {
raviTran <- getGoogleSheetFromSheetName("Ravi Transactions")
soundriTran <- getGoogleSheetFromSheetName("Soundari transactions")
combinedTran <- rbind(raviTran,soundriTran)
checkSchema(combinedTran,initTranSchema)
combinedTran
#mockTranInit()
}
#' Perform Data Quality Validations
#' @param x dataset from transformMint
#' @return data frame
#' @export
#' @examples
#' init <- getGoogleSheet("Ravi Transactions")
#' tr <- transformTran(init)
#' validateTran(tr)
setMethod(f="validate", signature=c("Expenses","logical"), definition = function(object,showErrors) {
ds <- object@transactions
#Make sure our schema is cln schema
checkSchema(ds,clnTranSchema)
#Clean the input dataset
vlds <- mutate(ds,
clnFail = if_else(is.na(clnTotal),'Total column is empty or malformed',NULL) )
#Add error indicator
dq <- mutate(vlds,
failed = if_else(is.na(clnFail)
,FALSE
,TRUE) )
#Arrange records
selDs <- arrange(dq,desc(clnDate))
#Remove unnecssary records
finDs <- select(selDs, -clnDate)
#Show only error output if corresponding option is chose
if(showErrors)
filter(finDs,failed)
else
finDs
})
#' Subset data based on weeks required
#' @param numWeeks No of weeks to subset the data on
#' @return data frame
#' @export
setMethod(f="subsetSummary", signature=c("Expenses","character"), definition = function(object,chrWeeks) {
ds <- object@summary
if(chrWeeks == "ALL")
weekData <- ds
else{
numWeeks <- as.numeric(chrWeeks)
weekData <- filter( ds,difftime(object@curDate,
as.Date(TransactionDate,"%m/%d/%Y"),
units="weeks") <= numWeeks )
}
weekData
})
#' Subset data based on weeks required
#' @param numWeeks No of weeks to subset the data on
#' @return data frame
#' @export
setMethod(f="subsetTransactions", signature=c("Expenses","character"), definition = function(object,chrWeeks) {
ds <- object@transactions
if(chrWeeks == "ALL")
weekData <- ds
else{
numWeeks <- as.numeric(chrWeeks)
weekData <- filter( ds,difftime(object@curDate,
as.Date(clnDate,"%m/%d/%Y"),
units="weeks") <= numWeeks )
}
weekData
})
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.