## Uncomment the next 3 lines to install the developer version of googleVis
# library(httr)
# )
# install.packages(c("devtools","RJSONIO", "knitr", "shiny", "httpuv"))
# library(devtools)
# install_github("mages/googleVis")
require(googleVis)
# install.packages('RPostgreSQL')
library(RPostgreSQL)

drv <- PostgreSQL()
con <- dbConnect(drv, host = "host_host", user= "user_user", password="password_password", dbname="dbname_dbname")

sql_query <- paste0("
with groups as (
  select 'Mono-User' as segment,'Active' as group
                    union all
                    select 'Newbie' as segment,'Active' as group
                    union all
                    select 'Optimizer' as segment,'Active' as group
                    union all
                    select 'User' as segment,'Active' as group
                    union all
                    select 'Lost card' as segment,'At risk' as group
                    union all
                    select 'Closure initiated' as segment,'At risk' as group
                    union all
                    select 'Silent' as segment,'At risk' as group
                    union all
                    select 'Closed contract' as segment,'Closed contract' as group
                    union all
                    select 'AML' as segment,'AML' as group
)
,original_set as (
SELECT
g1.group || to_char(ms1.mdate, ' YYMM') AS source,
g2.group || to_char(ms2.mdate, ' YYMM') AS target,
count(*) as count
FROM ms1
LEFT JOIN ms2 ON ms1.person_id = ms2.person_id AND ms2.mdate = ms1.mdate + INTERVAL '1 month'
left join groups g1 on ms1.client_category=g1.segment
left join groups g2 on ms2.client_category=g2.segment
WHERE 1 = 1
GROUP BY 1, 2

)
select source as source,
target as target,
count
from original_set
where source is not null and target is not null
order by 2,1;

                    ")

rs <- dbSendQuery(con, sql_query)
stats <- dbFetch(rs)

dbDisconnect(con)

require(googleVis)
plot(
  gvisSankey(stats, from="source",to="target", weight="count",
             options=list(
               width=1280, height=720,
               sankey="{link:{color:{fill:'lightblue'}},
                        iterations: 32,
                        node: { width: 10,
                                color: { fill: '#a61d4c' },
                                label: { fontSize: 14,
                                         color: '#871b47',
                                         bold: false,
                                         italic: false }
                              }
                      }"
             ))
)


## Focus: Movement from M1 to M6 directly
require(googleVis)
# install.packages('RPostgreSQL')
library(RPostgreSQL)

drv <- PostgreSQL()
con <- dbConnect(drv, host = "host_host", user= "user_user", password="password_password", dbname="dbname_dbname")

sql_query <- paste0("
with jan as (
    SELECT
                    person_id,
                    client_category || to_char(mdate, ' (Mon)') as segment
                    FROM ms
                    WHERE mdate = date(20160101)
), jun as (
                    SELECT
                    person_id,
                    client_category || to_char(mdate, ' (Mon)') as segment
                    FROM ms
                    WHERE mdate = date(20160601)
)
select jan.segment as source,
jun.segment as target,
count(*) as count
from jun inner join jan on jan.person_id=jun.person_id
group by 1,2
union ALL
select
jun.segment,
'total',
count(*) as count
from jun
group by 1,2

                    ")

rs <- dbSendQuery(con, sql_query)
stats <- dbFetch(rs)

dbDisconnect(con)

require(googleVis)
plot(
  gvisSankey(stats, from="source",to="target", weight="count",
             options=list(
               width=1280, height=720,
               sankey="{link:{color:{fill:'lightblue'}},
               iterations: 32,
               node: { width: 10,
               color: { fill: '#a61d4c' },
               label: { fontSize: 14,
               color: '#871b47',
               bold: false,
               italic: false }
               }
               }"
             ))
             )