Example of Embedding help Pop Ups in Hoonuit's Edvantage Dashboard Product

graphBlurbs.json

[
    { "name" : "# of Incidents by Year", 
      "id" : "411", 
      "graphStory" : "This table shows the number of incidents by year. It is broken down into incident type and shows three years.", 
      "chartType" : "Two-Way Table", 
      "dataQualityLinkUrl" : "https://icreports.fcps.net/ReportServer/Pages/ReportViewer.aspx?%2fConflictReports%2fICBehaviorErrorRept",
      "dataQualityLinkContent" : "Behavior Error Report", 
      "dataQualityReason" : "Are there any errors affecting my data that need to be addressed?", 
      "contactEmail" : "Jan Hatfield", 
      "contactSubjectLine" : "I have a question about Behavioral Incident Data", 
      "goodUseOfVisualization" : "You can use this chart to see what types of incidents occur the most frequently or have had a large increase from the previous year. This allows you to make decisions as to what types of interventions would be effective for your school(s).", 
      "lookOutFor" : "This table is updated as the data are entered and will change frequently until the end of the school year. It is important to note what date you access the data. It is inappropriate to use these data for making day-to-day decisions. There are other graphs which look at the data on a Dailey or hourly scale which would be more useful if that is your goal.", 
      "externalReferences" : [
        { "url" : "https://www.fcps.net/site/Default.aspx?PageID=5447", "resourceName" : "Safe Schools Office" }, 
        { "url" : "http://www.fcps.net/conduct", "resourceName" : "Student Code of Conduct" }, 
        { "url" : "http://www.pbis.org/", "resourceName" : "Technical Assistance Center on Positive Behavioral Interventions and Supports" }, 
        { "url" : "http://www.kycid.org/", "resourceName" : "Kentucky Center for Instructional Discipline" }, 
        { "url" : "http://www.casel.org/", "resourceName" : "Collaborative for Academic, Social and Emotional Learning" }, 
        { "url" : "http://www.kycss.org/index2.php", "resourceName" : "Kentucky Center for School Safety" }, 
        { "url" : "http://odcp.ky.gov/Pages/Agency-for-Substance-Abuse-Policy.aspx", "resourceName" : "Kentucky Agency for Substance Abuse Policy" },
        { "url" : "http://www.lexingtonhealthdepartment.org/", "resourceName" : "Lexington-Fayette County Health Department" }, 
        { "url" : "http://www.stopbullying.gov/", "resourceName" : "Stop Bullying Now national campaign" }, 
        { "url" : "https://www.fcps.net/Domain/2297", "resourceName" : "Interpretation Services" }] 
  }, 
  {
      "name" : "# of Behavioral Resolutions by Year", 
      "id" : "414", 
      "graphStory" : "This table shows the number of behavioral resolutions by year. It is broken down into resolution type and shows for three years.",
      "chartType" : "Two-Way Table", 
      "dataQualityLinkUrl" : "https://icreports.fcps.net/ReportServer/Pages/ReportViewer.aspx?%2fConflictReports%2fICBehaviorErrorRept",
      "dataQualityLinkContent" : "Behavior Error Report", 
      "dataQualityReason" : "Are there any errors affecting my data that need to be addressed?",
      "contactEmail" : "Jan Hatfield", 
      "contactSubjectLine" : "I have a question about Behavioral Resolutions", 
      "goodUseOfVisualization" : "This chart can be used to easily see a total of behavioral resolutions for a given year. Using this you can determine which resolutions were used and how frequently. If you have several blank resolutions you will want to find out why that is. There should be no blank resolutions.", 
      "lookOutFor" : "Not all schools enter this data at the same rate. It is important to know how the school(s) you are looking at enter its data so you can make sure you are using the most up-to-date information. A school that enters its data monthly will look very different compared to a school that enters its data Dailey. This data is not broken down by the time frame the resolution was used. You will want to look at one of the other graphs that examines this data in a more frequent method such as day-to-day or month-to-month to see more detailed and precise data.", 
      "externalReferences" : [
        { "url" : "https://www.fcps.net/site/Default.aspx?PageID=5447", "resourceName" : "Safe Schools Office" }, 
        { "url" : "http://www.fcps.net/conduct", "resourceName" : "Student Code of Conduct" }, 
        { "url" : "http://www.pbis.org/", "resourceName" : "Technical Assistance Center on Positive Behavioral Interventions and Supports" }, 
        { "url" : "http://www.kycid.org/", "resourceName" : "Kentucky Center for Instructional Discipline" }, 
        { "url" : "http://www.casel.org/", "resourceName" : "Collaborative for Academic, Social and Emotional Learning" }, 
        { "url" : "http://www.kycss.org/index2.php", "resourceName" : "Kentucky Center for School Safety" }, 
        { "url" : "http://odcp.ky.gov/Pages/Agency-for-Substance-Abuse-Policy.aspx", "resourceName" : "Kentucky Agency for Substance Abuse Policy" },
        { "url" : "http://www.lexingtonhealthdepartment.org/", "resourceName" : "Lexington-Fayette County Health Department" }, 
        { "url" : "http://www.stopbullying.gov/", "resourceName" : "Stop Bullying Now national campaign" }, 
        { "url" : "https://www.fcps.net/Domain/2297", "resourceName" : "Interpretation Services" }] 
  }
]  

graphInterpretations.json

{

    "prompt" : "<h4><span class=\"fcps-click-to-expand\"><i class=\"fa fa-expand\" aria-hidden=\"true\"></i></span>How do I interpret this plot?<sup>1</sup></h4>",
    "contents" :[ 
    { 
        "type" : "Bar Chart",
        "description" : "<div class=\"fcps-hidden-content\"><h5>What is a bar chart?</h5>",
        "how to read it" : "",
        "citation" : "<a href=\"http://book.visualisingdata.com/home\" target=\"_blank\"><sup>1</sup>.  Kirk, A. (2016).  <em>Data Visualisation: A Handbook for Data Driven Design.</em>  London, England: SAGE Publications Ltd. pp. 161</a></div>"
    },
    {
        "type" : "Clustered Bar Chart",
        "description" : "<div class=\"fcps-hidden-content\"><h5>What is a clustered bar chart?</h5>",
        "how to read it" : "",
        "citation" : "<a href=\"http://book.visualisingdata.com/home\" target=\"_blank\"><sup>1</sup>.  Kirk, A. (2016).  <em>Data Visualisation: A Handbook for Data Driven Design.</em>  London, England: SAGE Publications Ltd. pp. 162</a></div>"
    }]
}

updateBlurbs.py

import re
import json
import pandas as pd
from sqlalchemy import create_engine

#connection string for your database including username and password
connection_string = '' 


with open('./graphBlurbs.json', encoding='utf-8-sig') as json_file:
    graph_blurbs = json.load(json_file)

with open('./graphInterpretations.json', encoding='utf-8-sig') as json_file:
    graph_interpretations_json = json.load(json_file)

dataframe_content = []

for graph_blurb in graph_blurbs:
    for graph_type in graph_interpretations_json["contents"]:
        if graph_type["type"] == graph_blurb["chartType"]:
            description_html = graph_type["description"][33:]
            how_to_read_html = graph_type["how to read it"]

    externalReferences_html = ""
    for externalReference in graph_blurb["externalReferences"]:
        externalReferences_html += \
            """<li><a href="{url}" target="_blank">{resourceName}</a></li>"""\
            .format(url=externalReference["url"],
                    resourceName=externalReference["resourceName"])
    
    subtitle = """<div class="fcps-help" tabindex="1" align="right">Click for additional information
    <i class="fa fa-info-circle" aria-hidden="true" style="font-size: 1.5em;">
    </i>
</div>
<div id="{id}" class="fcps-help-contents" tabindex="1" style="opacity:0;">
    <label for="checkbox-{id}-1" class="fcps-label">
        <h4>
            <span class="fcps-click-to-expand">
                <i class="fa fa-expand" aria-hidden="true"></i>
            </span> What story is this chart telling?
        </h4>
    </label>
    <input id="checkbox-{id}-1" class="fcps-help-checkbox" type="checkbox" />
    <div class="fcps-hidden-content">{graphStory}</div>

    <label for="checkbox-{id}-2" class="fcps-label">
        <h4>
            <span class="fcps-click-to-expand">
                <i class="fa fa-expand" aria-hidden="true">
                </i>
            </span>How do I interpret this plot?<sup>1</sup>
        </h4>
    </label>
    <input id="checkbox-{id}-2" class="fcps-help-checkbox" type="checkbox" />
    <div class="fcps-hidden-content">
        {description_html} {how_to_read_html}
    </div>

    <label for="checkbox-{id}-3" class="fcps-label">
        <h4>
            <span class="fcps-click-to-expand">
                <i class="fa fa-expand" aria-hidden="true"></i>
            </span> How to use these data.
        </h4>
    </label>
    <input id="checkbox-{id}-3" class="fcps-help-checkbox" type="checkbox" />
    <div class="fcps-hidden-content">
        <ol>
            <li>{dataQualityReason} <a target="_blank" href="{dataQualityLinkUrl}">{dataQualityLinkContent}</a></li>
            <li>If you need additional suppor tin this area contact: <a href="mailto:{contactEmailWithDot}@fayette.kyschools.us?subject={contactSubjectLine}">{contactEmail}</a> or others from the same office</li>
            <li>EX. (appropriate interpretation/use) - {goodUseOfVisualtiation}</li>
            <li>EX. (inappropriate interpretation/use) - {lookOutFor}</li>
        </ol>
    </div>
    <h4>Additional Resources</h4>
    <ul>
        {externalReferences_html}
    </ul>
</div>
    """.format(
        id=graph_blurb["id"],
        graphStory=graph_blurb["graphStory"],
        description_html=description_html,
        how_to_read_html=how_to_read_html,
        dataQualityReason=graph_blurb["dataQualityReason"],
        dataQualityLinkUrl=graph_blurb["dataQualityLinkUrl"],
        dataQualityLinkContent=graph_blurb["dataQualityLinkContent"],
        contactEmailWithDot='.'.join(graph_blurb["contactEmail"].split()),
        contactSubjectLine=graph_blurb["contactSubjectLine"],
        contactEmail=graph_blurb["contactEmail"],
        goodUseOfVisualtiation=graph_blurb["goodUseOfVisualization"],
        lookOutFor=graph_blurb["lookOutFor"],
        externalReferences_html=externalReferences_html)
    dataframe_content.append({'object_id': graph_blurb["id"], 'subtitle': subtitle})

df_with_new_subtitles = pd.DataFrame(dataframe_content)
engine = create_engine(connection_string)
con = engine.connect()
query_string = \
'''SELECT 
	object_id,
	CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), OBJECT_PROPS)) as 'original_decoded_object_props'
FROM [DCV-DWH-DB01].[K12INTEL_PORTAL_DEV].K12INTEL_PORTAL.PTL_OBJECTS'''
rs = con.execute(query_string)
df_from_data_base = pd.DataFrame(rs.fetchall())
df_from_data_base.columns = rs.keys()
df_from_data_base['object_id'] = df_from_data_base['object_id'].astype(int)
df_with_new_subtitles['object_id'] = df_with_new_subtitles['object_id'].astype(int)

new_df = pd.merge(df_from_data_base, df_with_new_subtitles, on='object_id') 

def change_string(row):
    if (row['original_decoded_object_props'] is not None) and (row['subtitle'] is not None):
        pattern = re.compile(r'<property name="SUBTITLE"><!\[CDATA\[.*?\]\]\></property>', re.MULTILINE|re.DOTALL)
        new_string = re.sub(pattern, 
            '<property name="SUBTITLE"><![CDATA[{}]]></property>'.format(row['subtitle']),
            row['original_decoded_object_props'])
        return new_string
    return None

new_df['new_decoded_object_props'] = new_df.apply(change_string, axis=1)

new_df.to_sql('table_blurbs', con, if_exists="replace")

query_string_create_column = \
'''ALTER TABLE table_blurbs 
    ADD original_binary_object_props VARBINARY(MAX) NULL, new_binary_object_props VARBINARY(MAX) NULL;'''
query_string_add_original_binary_object_props = \
'''UPDATE table_blurbs
SET original_binary_object_props = CONVERT(VARBINARY(MAX), original_decoded_object_props);'''
query_string_add_new_binary_object_props = \
'''UPDATE table_blurbs
SET new_binary_object_props = CONVERT(VARBINARY(MAX), new_decoded_object_props);'''
con.execute(query_string_create_column)
con.execute(query_string_add_original_binary_object_props)
con.execute(query_string_add_new_binary_object_props)

query_to_inject_into_portal_objects = \
'''
Update K12INTEL_PORTAL_DEV.K12INTEL_PORTAL.PTL_OBJECTS
 
Set K12INTEL_PORTAL_DEV.K12INTEL_PORTAL.PTL_OBJECTS.OBJECT_PROPS = match.new_binary_object_props
 
From (Select v.OBJECT_ID, v.OBJECT_PROPS, f.new_decoded_object_props, f.new_binary_object_props
           from K12INTEL_PORTAL_DEV.K12INTEL_PORTAL.PTL_OBJECTS v
           Inner Join table_blurbs f on v.OBJECT_ID = f.OBJECT_ID
           ) match
          
where K12INTEL_PORTAL_DEV.K12INTEL_PORTAL.PTL_OBJECTS.OBJECT_ID = match.OBJECT_ID
'''
con.execute(query_to_inject_into_portal_objects)

con.close()
print("finished")