bUdgEtRack/Pre_runner.Rmd

---
title: "Pre"
author: "Scary Scarecrow"
date: '2022-06-27'
output: html_document
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
library(dplyr)
library(echarts4r)
library(lubridate)
library(shinymanager)
```

## Credentials

```{r}
credentials <- data.frame(
  user = c("shiny", "asitav", "rigo", "aldo"),
  password = c("lanubia@2021", "lanubia@2021","lanubia@2021","lanubia@2021"),
  admin = c(FALSE, TRUE, FALSE, FALSE),
  email = c("hello@asitavsen.com","asitav.sen@lanubia.com","rigo.selassa@lanubia.com","aldo.silvano@lanubia.com"),
  stringsAsFactors = FALSE
)

create_db(
  credentials_data = credentials,
  sqlite_path = "./cred.sqlite", # will be created
  passphrase = "kJuyhG657Hj&^%gshj*762hjsknh&662"
)
```


## DB

```{r}
connec <- dbConnect(
  RPostgres::Postgres(),
  dbname = dsn_database,
  host = dsn_hostname,
  port = dsn_port,
  user = dsn_uid,
  password = dsn_pwd
)
```



```{r}
#dat<-read.csv("./data/initial.csv")
#lims<-read.csv("./data/dev.limits.csv")
dat.1<-
  dat |> 
  mutate(devia=Actual-Plan) |> 
  mutate(devia.per=round(devia/Plan,2))
#dbCreateTable(connec, "calculated", dat.1)
dbWriteTable(connec, "calculated", dat.1, append=TRUE)
dbWriteTable(connec, "limits", lims, append=TRUE)

#dbRemoveTable(connec,"calculated")
dat<-dbGetQuery(
  connec,
  'SELECT * FROM calculated'
)

lims<-dbGetQuery(
  connec,
  'SELECT * FROM limits'
)

#dbReadTable(connec, "calculated")

exp<-
  dat |> 
  inner_join(lims, by=c("GL.account")) |> 
  mutate(act.req=ifelse(devia.per>Limit & devia > 500,T,F)) |> 
  filter(act.req) |> 
  select(1:3) |> 
  mutate(explanation=c("Cyberattack","Overwork","Interview","Maintenance","Quarterly Report","New Legislation"))

dbWriteTable(connec, "explanations", exp, append=TRUE)
exp<-dbGetQuery(
  connec,
  'SELECT * FROM explanations'
)

approvals<- exp |> mutate(approved=F)

dbWriteTable(connec, "approvals", approvals, overwrite=T)

approvals<-dbGetQuery(
  connec,
  'SELECT * FROM approvals'
)


  dat |> 
  inner_join(lims, by=c("GL.account")) |> 
  mutate(act.req=ifelse(devia.per>Limit & devia > 500,T,F)) |> 
  filter(act.req) |> 
  left_join(exp, by=c("month"="month","Cost.center"="Cost.center","GL.account"="GL.account"))

  
  emailsids<-dat |> 
    select(Cost.center, GL.account) |> 
    distinct() |> 
    mutate(email=c("asitav.sen@lanubia.com"))
dbWriteTable(connec, "emails", emailsids, overwrite=T)  
emailsids<-dbGetQuery(
  connec,
  'SELECT * FROM emails'
)

sel.emails<- emailsids |> 
  filter(email=="asitav.sen@lanubia.com")
  
  dat |> 
  inner_join(lims, by=c("GL.account")) |> 
  mutate(act.req=ifelse(devia.per>Limit & devia > 500,T,F)) |> 
  filter(act.req) |> 
  left_join(sel.emails, by=c("Cost.center"="Cost.center","GL.account"="GL.account")) |> 
    left_join(exp, by=c("month"="month","Cost.center"="Cost.center","GL.account"="GL.account")) |> 
    filter(is.na(explanation) | explanation=="") |> 
    filter(email=="asitav.sen@lanubia.com") |> 
    select(-c(9,10))

  exp
```



```{r}

dat.2<-
    dat |> 
    mutate(month=ym(month)) |> 
    group_by(month) |> 
    summarise(Plan=sum(Plan),Actual=sum(Actual)) |> 
    mutate(devia=Actual-Plan) |> 
    mutate(deviation.percent=round(devia*100/Plan,2))
  
  e_chart(dat.2, x=month) |> 
    e_line(serie = deviation.percent, smooth=T, color="cyan") |> 
    e_area(serie = deviation.percent, smooth=T, color="gray") |> 
    e_axis_labels(x = "month", y="Deviation") |> 
    e_format_y_axis(suffix = " %") |> 
    e_title("Deviation", "Selected Cost Centers") |> 
    e_tooltip() |> 
    e_legend(right = 100) |> 
    e_datazoom(x_index = c(0, 1)) |> 
    e_toolbox_feature(feature = c("saveAsImage","dataView")) |> 
    e_theme("chalk")
  
    history<-dat.2 |> select(month,deviation.percent) |> rename(ds=month, y=deviation.percent)
    model <- prophet::prophet(history)
    future <- prophet::make_future_dataframe(model, periods = 2)
    forecast <- predict(model, future)
  
dat |> 
  filter(month==max(month)) |> 
  mutate(cost_gl=paste0(Cost.center,"_",GL.account)) |> 
  group_by(cost_gl) |> 
  summarise(Plan=sum(Plan),Actual=sum(Actual)) |> 
    mutate(devia=Actual-Plan) |> 
    mutate(deviation.percent=round(devia*100/Plan,2)) |> 
  arrange(desc(deviation.percent)) |> 
  e_charts(cost_gl) |> 
  e_bar(Plan, name = "Plan", color="gray") |> 
  e_step(Actual, name = "Actual", color="red") |> 
  e_axis_labels(x = "GL+Cost Center", y="Deviation") |> 
    e_title("Selected Cost Centers") |> 
    e_tooltip() |> 
    e_legend(right = 100) |> 
    e_datazoom(x_index = 0, type = "slider") |> 
  e_datazoom(y_index = 0, type = "slider") |> 
    e_toolbox_feature(feature = c("saveAsImage","dataView")) |> 
    e_theme("chalk")
```
```{r}
unique(dat$Cost.center) |> saveRDS("./data/costcenters.RDS")
unique(dat$GL.account) |> saveRDS("./data/glaccounts.RDS")
unique(dat$month) |> saveRDS("./data/months.RDS")
lims
```





```{r}
dat.dev<-dat |> 
  filter(devia>0) |> 
  select(2,3,6)

e_charts(dat.dev) |> 
  e_pie(devia)

blastula::create_smtp_creds_file(
  file = "email_creds",
  user = "apikey",
  host = "smtp.sendgrid.net",
  port = 465,
  use_ssl = TRUE
)

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
```