library(lubridate)
library(gt)
library(gtExtras)
library(dplyr)
options(width = 200)
df <- read.csv(file = './2022.csv')
df = df[, c("Date","Type","Description","Value","Balance")]


df$TrueDate <- ifelse(df$Type == "POS", substr(df$Description, start = 6, stop = 10), df$Date)

df$TrueDate <- ifelse(df$Type == "POS", format(as.Date(paste0(df$TrueDate, "22"), format = "%d%b%y"), "%d-%b-%y"),df$TrueDate)

df$Description = ifelse(df$Type =="POS", substr(df$Description, 15, nchar(df$Description)),df$Description)

df <- df[!grepl("DEC21", df$Description), ]

df <- df[!grepl("LOANPAD",df$Description),]
df <- df[!grepl("CROWDSTACKER",df$Description),]
df <- df[!grepl("CROWD2FUND",df$Description),]
df <- df[!grepl("LANDLORDINVEST", df$Description),]
df <- df[!grepl("VANGUARD",df$Description),]
df <- df[(!grepl("SMARKETS",df$Description)),]
df <- df[(!grepl("Crowdstacker", df$Description)),]
df <- df[(!grepl("TRADING", df$Description)),]
df <- df[(!grepl("SALARY", df$Description)),]
df <- df[(!grepl("MARGARET SEYMOUR", df$Description)),]
df <- df[(!grepl("KAREN", df$Description)),]
df <- df[(!grepl("96000N", df$Description)),]
#df <- df[(!grepl("STARLING", df$Description)),]
df <- df[(!grepl("701001", df$Description)),]


df[!grepl("353-12477661", df$Description),]


df$Date = df$TrueDate
df$Date=as.POSIXct(df$Date, format = "%d-%b-%y")
df$Date = format(df$Date, "%Y-%m-%d %H:%M:%S")


df <- df[order(df$Date), ]

df$num <- seq_len(nrow(df))

df$Month <- month(df$Date, label = TRUE)
df$Day   = yday(df$Date)

table = gt(df) %>%
gt_theme_538() %>%

tab_row_group(rows = which(df$Month == "Dec"),label = paste0("December")) %>%
tab_row_group(rows = which(df$Month == "Nov"),label = paste0("November")) %>%
tab_row_group(rows = which(df$Month == "Oct"),label = paste0("October")) %>%
tab_row_group(rows = which(df$Month == "Sep"),label = paste0("September")) %>%
tab_row_group(rows = which(df$Month == "Aug"),label = paste0("August")) %>%
tab_row_group(rows = which(df$Month == "Jul"),label = paste0("July")) %>%
tab_row_group(rows = which(df$Month == "Jun"),label = paste0("June")) %>%
tab_row_group(rows = which(df$Month == "May"),label = paste0("May")) %>%
tab_row_group(rows = which(df$Month == "Apr"),label = paste0("April")) %>%
tab_row_group(rows = which(df$Month == "Mar"),label = paste0("March")) %>%
tab_row_group(rows = which(df$Month == "Feb"),label = paste0("February")) %>%
tab_row_group(rows = which(df$Month == "Jan"),label = paste0("January")) %>%

summary_rows(groups=everything(),columns = Value,fns = list(total="sum")) %>%
data_color(columns = Value,method = "numeric",palette = "Reds",domain = c(0, -200),reverse = TRUE) %>%
cols_hide(columns = c(Month,Balance)) %>%
tab_options(table.font.size="10px", data_row.padding = "0px") %>%
tab_header(title = gt::html("<h1 style='color:red'>2022 Tranactions</h1>")) %>%
tab_style(style = list(cell_fill(color = "#FFCC00")),locations = cells_row_groups())
gtsave(table, file = "2022_table.html")

#df <- mutate(df, Month = format(Date, "%Y-%m"))

sum <- df %>%
  group_by(Day) %>%
  summarize(DailySum = sum(Value, na.rm = TRUE))

#salary <- df %>%
#  filter(grepl("SALARY", Description, ignore.case = TRUE)) %>%
#  group_by(Month) %>%
#  summarize(Monthly_Salary = sum(Value, na.rm = TRUE))

#expenses <- df %>%
#  filter(Value < 0) %>%
#  group_by(Month) %>%
#  summarize(Expenses = sum(Value, na.rm = TRUE))
#
#monthly_breakdown=merge(salary,expenses,by = "Month", all.x = TRUE)
#
print(sum,n=40)

gtab = gt(sum) %>%
gt_theme_538() %>%
#summary_rows(groups=everything(),columns = Value,fns = list(total="sum")) %>%
data_color(columns = DailySum,method = "numeric",palette = "Reds",domain = c(0, -300),reverse = TRUE) %>%
#cols_hide(columns = c(Month,Balance)) %>%
tab_options(table.font.size="10px", data_row.padding = "0px") %>%
tab_header(title = gt::html("<h1 style='color:red'>2022 Tranactions</h1>"))
#tab_style(style = list(cell_fill(color = "#FFCC00")),locations = cells_row_groups())


gtsave(gtab,file = "2022_sum.html")




write.csv(df[c("Date","Type","Description","Value")], file = '2022_clean.csv', row.names = FALSE)
head(df)