# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3 (ipykernel)
#     language: python
#     name: python3
# ---

# # Querying Hierarchical Data Using the `reach` Action in the `NETWORK` Actionset

# In this demonstration, we load a sample company's organization structure into our CAS server, and query it to identify all employees in a single manager's reporting structure.
#
# This example emulates the same query, but conducted using recursive SQL queries, as shown in [Recursive Query with PROC SQL](https://kansascode.blogspot.com/2021/06/recursive-query-with-proc-sql.html?m=1) as well as [Learn PostgreSQL Recursive Query By Example](https://www.postgresqltutorial.com/postgresql-recursive-query/).
#
# We show how through use of the `reach` action, we identify all employees who report to a manager named __<NAME>__. Traversal algorithms allow us to avoid recursion, making our code simpler to understand and debug.
#
# ----------------
#
# The basic flow of this notebook is as follows:
# 1. Load the organizational structure into a Pandas DataFrame as a set of links.
# 2. Connect to a CAS server and upload our DataFrame.
# 3. Execute the `reach` action and fetch the results.
# 4. Compare results with the known results from the examples referenced above.
#
# ----------------
# __Prepared by:__
# <NAME> (<i class="fa fa-github" aria-hidden="true"></i>: [dtherrick](www.github.com/dtherrick))

# ## Imports
#
# The modules below are needed for this exercise.
#
# | Module             | Description                                                                        |
# |:-------------------|:----------------------------------------------------------------------------------:|
# | `os`               | Allows access to environment variables.                                            |
# | `swat`             | SAS Python module that orchestrates communication with a CAS server.               |
# | `pandas`           | Data management module we use for preparation of local data.                       |
# | `graphviz.Digraph` | Used to display the organizational structure with the results of our reach query.  | 

# +
import os

import swat
import pandas as pd

from graphviz import Digraph
# -

# ## Sample Data
#
# We take our sample data from the original post about recursive SQL.
#
# | `employee_id` |     `full_name`     | `manager_id` |
# |:-------------:|:-------------------:|:------------:|
# |       1       | '<NAME>'     |     NULL     |
# |       2       | '<NAME>'       |       1      |
# |       3       | '<NAME>'       |       1      |
# |       4       | '<NAME>'         |       1      |
# |       5       | '<NAME>'         |       1      |
# |       6       | '<NAME>'      |       2      |
# |       7       | '<NAME>'     |       2      |
# |       8       | '<NAME>'         |       2      |
# |       9       | '<NAME>'   |       2      |
# |      10       | '<NAME>' |       3      |
# |      11       | '<NAME>'      |       3      |
# |      12       | '<NAME>'   |       3      |
# |      13       | '<NAME>'      |       3      |
# |      14       | '<NAME>'      |       4      |
# |      15       | '<NAME>'     |       4      |
# |      16       | '<NAME>'       |       7      |
# |      17       | '<NAME>'    |       7      |
# |      18       | '<NAME>'      |       8      |
# |      19       | '<NAME>'   |       8      |
# |      20       | '<NAME>'    |       8      |
#
# For our purposes, let's put that data into a master dataframe, which we can use both in our reach calculations as well as for verifications and visualization later in this exercise.

# +
# We use a list of tuples, along with a list of column names, to create the master dataframe.
colNames = ["employee_id", "full_name", "manager_id"]
data = [
    (1, "<NAME>", 0),
    (2, "<NAME>", 1),
    (3, "<NAME>", 1),
    (4, "<NAME>", 1),
    (5, "<NAME>", 1),
    (6, "<NAME>", 2),
    (7, "<NAME>", 2),
    (8, "<NAME>", 2),
    (9, "<NAME>", 2),
    (10, "<NAME>", 3),
    (11, "<NAME>", 3),
    (12, "<NAME>", 3),
    (13, "<NAME>", 3),
    (14, "<NAME>", 4),
    (15, "<NAME>", 4),
    (16, "<NAME>", 7),
    (17, "<NAME>", 7),
    (18, "<NAME>", 8),
    (19, "<NAME>", 8),
    (20, "<NAME>", 8), 
]
# create the dataframe from the above source data.
dfMasterData = pd.DataFrame(data, columns=colNames)

# display the table, but we don't care about the index for this exercise.
dfMasterData.style.hide_index()
# -

# ## Connect to our Viya (CAS) server
#
# We now need to connect to our CAS server. Contact your SAS administrator for the proper credentials to connect to CAS using `python-swat`.
#
# As a convention, I keep my CAS host and port set as environment variables. This allows me to both avoid placing user-specific data in notebooks, as well as adds a layer of security.
#
# For ease-of-reading, I assign those environment variables into `host` and `port` variables, then pass them into the connection statement.
#
# Once connected, we need access to the `PROC NETWORK` actions. In CAS terminology, that is an `actionset` so we load that now.

# +
host = os.environ['CAS_HOST_ORGRD']
port = int(os.environ['CAS_PORT'])

# Connect to the server
conn = swat.CAS(host, port)

# Load the actionsets we need
conn.loadactionset('network')

# + [markdown] heading_collapsed=true
# ## Load source tables into CAS
#
# All of the `network` actions require a graph table. In this exercise, we simply need to pass the action a `LinkSetIn` table that represents the relationships between the employees, and a `NodeSubsetIn` table that represents the specific nodes whose reach we wish to calculate. 
#
# As we defined in the master data above, we only need to pass the `employee_id` and `manager_id` values from each row to create the links table. Since __<NAME>__ does not have a manager, we want to skip his record in the links table we upload to CAS. The easiest way is to create an intermediate dataframe using the pandas `loc` method to exclude the first row, and limit to only the `employee_id` and `manager_id` columns.
#
# For the `NodeSubsetIn` table, we create an intermediate dataframe with a specific format. Its source must be a list of dicts, where each dictionary has two keys: `node` and `reach`. `node`'s value is the identifier of the node whose reach we calculate. `reach` is the order of reach calculations we want to calculate. Since we are only calculating for one source node, `reach`'s value is 1.
#
# Once the intermediate dataframes are created, we upload them to CAS using the `upload` method. The `casout=` argument allows us to define the names of the CAS tables.

# + hidden=true
dfLinkSetIn = dfMasterData.loc[dfMasterData.employee_id>1, ['employee_id', 'manager_id']]
dfNodeSubsetIn = pd.DataFrame([{'node': 2, 'reach': 1}])

swat.options.cas.print_messages=False

_ = conn.upload(dfLinkSetIn, casout=dict(name='LinkSetIn', replace=True))
_ = conn.upload(dfNodeSubsetIn, casout=dict(name='NodeSubsetIn', replace=True))
# -

# ## Find all employees who report to <NAME>
#
# We can find all of Megan's subordinates using the `reach` action.
#
# The reach action takes several options:
# * `links`: The name of the source table that contains the full set of links that describe our graph.
# * `nodessubset`: The name of the source table that contains the nodes and reach we wish to search against.
# * `direction`: Define whether the graph in the `links` option is a directed or undirected graph. For this exercise, we must explicitly state that this is a directed graph using the string `directed`.
# * `linksvar`: We include this option because we did not name our columns `from` and `to`. (If we had, we could ignore this option). For our calculation, we pass a dictionary where `manager_id` is the `from` value, and `employee_id` is the `to` value.
# * `maxreach`: an integer value that defines how many levels deep we should calculate the reach. We use 2 in this case.
# * `outReachNodes`: an output table with the nodes that are included in the reach calculations.
# * `outReachLinks`: an output table with the link pairs that are included in the reach calculations.
# * `outCounts`: an output summary table that has the total count of nodes included in the reach calculations.

# %%time
conn.network.reach(
    direction     = "directed",
    links         = {"name":"LinkSetIn"},
    linksvar      = {"from": "manager_id", "to": "employee_id"},
    nodessubset   = {"name":"NodeSubSetIn"},
    outReachNodes = {"name":"ReachNodes",  "replace":True},
    outReachLinks = {"name":"ReachLinks",  "replace":True},
    outCounts     = {"name":"ReachCounts", "replace":True},
    maxreach      = 2)

# ## Review the output tables
#
# If the reach action was successful, it will output some summary and log information in the notebook. We asked it to generate three tables, so let's just fetch those tables and verify that the data looks OK.
#
# For the `ReachLinks` table, a quick pass at the first four rows shows Megan has four direct reports. If we glance back at the master data above, we see that, yes, her employee id is 2, and the `ReachLinks` table shows four employees with a manager id of 2.
#
# For `ReachCounts`, we see that node 2 connects to 10 nodes. This is good, since there are 20 total nodes - we know Megan doesn't manage everyone.
#
# For `ReachNodes`, we simply verify that there are 10 rows (nodes) included.
#
# Based on the quick verification, all three tables look good.

conn.fetch('ReachLinks')

conn.fetch('ReachNodes')

conn.fetch('ReachCounts')

# ## Verify that our call to the reach action matches the output of recursive SQL
#
# From the initial PostgreSQL recursive SQL article, we expect these results:
#
#  employee_id  | manager_id   |    full_name
# -------------:|:------------:|:-----------------
#            2  |          1   | <NAME>
#            6  |          2   | <NAME>
#            7  |          2   | <NAME>
#            8  |          2   | <NAME>
#            9  |          2   | <NAME>
#           16  |          7   | <NAME>
#           17  |          7   | <NAME>
#           18  |          8   | <NAME>
#           19  |          8   | <NAME>
#           20  |          8   | <NAME>
#           
# Let's use the `ReachNodes` table and the `dfMasterData` dataframe to try to reproduce this table.
#
# Our approach:
# 1. Create a list of only the node id's in the `ReachNodes` returned by the reach action. A simple list comprehension accomplishes this, in the `report_list` variable.
# 2. Then, use that list with the master data to generate a boolean pandas series, where values are True if they are in Megan's reporting reach, False if not.
# 3. Filter the master dataframe to create a `Reports` table we can use to compare.
# 4. Display that final reports table.
#
# We see that, yes, the reach action provides the same results as the recursive SQL statement.

# +
report_list = [int(i) for i in  conn.fetch('ReachNodes')['Fetch']['node'].to_list()]

reports_series = dfMasterData.employee_id.isin(report_list)
dfReports = dfMasterData[reports_series]

dfReports.style.hide_index()
# -

# ## Bonus: Verify Results Visually
#
# Finally, let's produce a tree diagram of our organizational structure, and highlight the employees in Megan's reporting structure. This approach allows us to quickly verify whether the reach action produced the correct reporting structure.
#
# We use `graphviz` since it provides an easy means to programmatically create a directed network graph diagram. Since we can't directly translate pandas dataframes to graphviz dot syntax, we need to convert our dataframes to Python lists.
#
# Our approach is as follows:
# 1. Create a `graph` object from the master dataframe. Calling the `to_dict` method with `orient='records'` set returns a list of dictionaries, one for each row in the dataframe.
# 2. Create two lists of tuples - one for the reporting list, and one for everyone else. We simply use list comprehensions filtered by the `report_list` we created above to generate these variables.
# 3. Define our graphviz object as a `Digraph`.
# 4. Add the nodes in the reporting structure. For our purposes, we highlight by filling these nodes with light blue.
# 5. Add all remaining nodes. Leave them unfilled.
# 6. Use the full graph - which already has the link relationships - to add the links to our visualization.
# 7. Since this is a wide image, we create a `final_plot` object in which we pass our dot language and use the `unflatten` option with a `stagger` value of 4.
# 8. Finally, display the plot.

# +
graph = dfMasterData.to_dict(orient='records')

report_nodes = [(node['employee_id'], node['full_name']) for node in graph if node['employee_id'] in report_list]
other_nodes = [(node['employee_id'], node['full_name']) for node in graph if node['employee_id'] not in report_list]

dot = Digraph()

for item in report_nodes:
    dot.node(str(item[0]), item[1], style='filled', fillcolor='lightblue')

for item in other_nodes:
    dot.node(str(item[0]), item[1])

# note that we skip the first row in "graph" because that is the head of the tree with no manager. We don't need a phantom link called "0"
for pair in graph[1:]:
    dot.edge(str(pair['manager_id']), str(pair['employee_id']))

final_plot = dot.unflatten(stagger=4)

final_plot
# -

# ## Clean up everything. 
#
# Make sure we know what tables we created, drop them, and close our connection.
# (This is probably overkill, since everything in this session is ephemeral anyway, but good practice nonetheless.

# +
table_list = conn.tableinfo()["TableInfo"]["Name"].to_list()

for table in table_list:
    conn.droptable(name=table, quiet=True)

conn.close()
# -

# ## Conclusion
#
# In this notebook, we used the `reach` action in the `network` actionset for SAS Viya to determine all employees in a single person's reporting structure. We checked our results against the same calculations performed by recursive SQL statements in both PostgreSQL and SAS `PROC SQL`. 
#
# Both a results table and a network diagram demonstrate that the reach action results are equivalent to the recursive SQL statements. Using the reach action is both less verbose, and requires less debugging and design than developing custom recursive queries.

StarcoderdataJupyterScriptsDedupFiltered