app.py

# pylint: disable=E1120
# See: https://github.com/streamlit/streamlit/issues/1536
# See: https://discuss.streamlit.io/t/error-no-value-for-argument-body-in-method-call-vs-code/1851/6

importos
importtime
importjson
importsqlite3
fromdatetimeimportdatetime

importstreamlitasst
importpandasaspd
importnumpyasnp
importaltairasalt
importrequests

fromgenerate_dbimportGitHistoryConverter
fromsettingsimportConfig, TERM_CODES_TO_CONFIG

#
# Data and term configuration
#

ROOT='./'
TERM_CODES= {
'Foothill, Winter 2021' : '202131',
'De Anza, Winter 2021' : '202132',
'Foothill, Fall 2020' : '202121',
'De Anza, Fall 2020' : '202122',
'Foothill, Summer 2020' : '202111',
'De Anza, Summer 2020' : '202112',
}

# Workaround for a timezone bug in streamlit
# ORIGINAL_TIMEZONE = time.tzname[time.daylight]
# # os.environ['ORIG_TZ'] = time.tzname[time.daylight]
# try:
# ORIGINAL_TIMEZONE = time.tzname[1]
# # os.environ['ORIG_TZ']
# except IndexError:
# ORIGINAL_TIMEZONE = time.tzname[0]

# print(ORIGINAL_TIMEZONE)

# https://github.com/streamlit/streamlit/issues/1061#issuecomment-642057212
os.environ['TZ'] ='UTC'

#
# Data generator
#

progress_bar=None

defsetup_progress_bar(name):
globalprogress_bar

st.sidebar.text(name)
progress_bar=st.sidebar.progress(0)

defupdate_progress_bar(value):
progress_bar.progress(value)

defgenerate_data(settings, interval, full_reset):
st.sidebar.markdown('### Generating Data')
delos.environ['TZ']
# os.environ['TZ'] = ORIGINAL_TIMEZONE
GitHistoryConverter(
settings,
Config(interval, full_reset),
update_progress=update_progress_bar,
setup_progress=setup_progress_bar
 ).convert()
os.environ['TZ'] ='UTC'
st.caching.clear_cache()
st.balloons()

#
# Database Utilities
#

@st.cache(allow_output_mutation=True)
defconnect_db(term):
# TODO: check thread safety
conn=sqlite3.connect(f'{ROOT}db/temp_{term}.sqlite3', check_same_thread=False)
c=conn.cursor()
returnc

@st.cache(hash_funcs={sqlite3.Cursor:id})
defdata_exists(c):
tableExists=c.execute(
'SELECT name FROM sqlite_master WHERE type="table" AND name="classes"'
 ).fetchone()
iftableExistsandtableExists[0]:
returnTrue
returnFalse

#
# Queries
#

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_all_classes(c):
returnc.execute('''
 SELECT
 time,
 SUM(seats),
 SUM(wait_seats),
 COUNT(case status when "Open" then 1 else null end) as open_classes,
 COUNT(case status when "Waitlist" then 1 else null end) as waitlist_classes,
 COUNT(case status when "Full" then 1 else null end) as full_classes
 FROM classes
 GROUP BY time;
 ''').fetchall()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_total_classes(c):
returnc.execute('''
 SELECT
 time,
 COUNT(CRN)
 FROM classes
 GROUP BY time;
 ''').fetchall()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defcount_times(c):
returnc.execute('SELECT COUNT(time) FROM classes;').fetchone()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_available_crn(c):
returnc.execute('SELECT DISTINCT CRN FROM classes;').fetchall()

# TODO: hash
# NOTE: this does not currently work beause we do not store dept, course, and section
defget_one_class(c, dept, course, section):
returnc.execute('SELECT * FROM classes WHERE dept = ? and course = ? and section = ?', [dept, course, section]).fetchall()

# TODO: hash
defget_one_class_by_crn(c, crn):
returnc.execute('SELECT * FROM classes WHERE CRN = ?', [crn]).fetchall()

#
# Utilities
#

# Create a date from an SQLite datetime string
defdt(date_string):
returndatetime.strptime(date_string, '%Y-%m-%d %H:%M:%S')

#
# Streamlit App
#

# Sidebar page navigation
st.sidebar.markdown('## NAVIGATION')
page=st.sidebar.radio('Select a page:', ['Home', 'API', 'Quickstart', 'About'])

# Home page, with all its graphs
ifpage=='Home':
'''
 # Data Analysis
 ### By [Open Course API](https://github.com/OpenCourseAPI/)
 ####
 Tip: Use the sidebar to change the term and control other settings.
 '''

# Sidebar settings
st.sidebar.markdown('## SETTINGS')
term=st.sidebar.selectbox(
'Choose a term: ', list(TERM_CODES.keys())
 )
term_config=TERM_CODES_TO_CONFIG[TERM_CODES[term]]
# show_debug_info = st.sidebar.checkbox('Show debug information')
show_advanced_options=st.sidebar.checkbox('Show advanced options')

# Data generation options
st.sidebar.markdown('## DATA')

interval=60
ifshow_advanced_options:
interval=st.sidebar.slider('Interval time (in minutes)', value=60, min_value=5, max_value=60, step=5)

ifst.sidebar.button('Update'):
generate_data(term_config, interval, False)

ifst.sidebar.button('Regenerate'):
generate_data(term_config, interval, True)

# Main Page Content

# Load database
c=connect_db(TERM_CODES[term])

# Generate data if necessary
ifnotdata_exists(c):
generate_data(term_config, interval, True)

# Get all class data aggregated by time
class_data=get_all_classes(c)
times= [dt(x[0]) forxinclass_data]
# total_times = countTimes(c)[0]

l=len(class_data)
start_date=landclass_data[0][0]
end_index= (l-1) or0
end_date=landclass_data[end_index][0]

f'''
 ## {term}

 Selected term data info:

 | Term | (selected term) |
 | --- | --- |
 | Start | {dt(start_date).strftime('%b %d, %Y %I:%M %p')} |
 | End | {dt(end_date).strftime('%b %d, %Y %I:%M %p')} |

 '''

'''
 ## All Classes
 ### Raw Data
 '''

df=pd.DataFrame(
 [x[1:] forxinclass_data],
index=times,
columns=['Seats', 'Wait Seats', 'Open Classes', 'Waitlist', 'Full']
 )
df

'''
 ### Open Seats By Time
 '''

df=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=times,
columns=['Seats', 'Wait Seats']
 )
st.line_chart(df)

df2=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=pd.DatetimeIndex(times),
columns=['Seats', 'Wait Seats']
# )
 ).resample('D').mean()
df=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=pd.DatetimeIndex(times),
columns=['Seats', 'Wait Seats']
# ).shift()
 ).resample('D').mean().shift()
st.line_chart(df.subtract(df2))

'''
 ### Class Status By Time
 '''

df=pd.DataFrame(
 [x[3:6] forxinclass_data],
index=times,
columns=['Open', 'Waitlist', 'Full']
 )
st.area_chart(df)

'''
 ### Total Class Count By Time
 '''

total_classes=get_total_classes(c)
df=pd.DataFrame(
 [x[1:] forxintotal_classes],
index=[dt(x[0]) forxintotal_classes],
columns=['Classes']
 )
st.line_chart(df)

'''
 ### View Course History
 '''

# dept = st.text_input('Department', 'CS')
# course = st.text_input('Course', '1A')
# section = st.text_input('Section', '1Z')
# crs = get_one_class(c, dept, course, section)

crn=st.text_input('Enter a CRN', 10152)
crs=get_one_class_by_crn(c, crn)

ifcrsandlen(crs) >0:
df=pd.DataFrame(
 [x[3:5] forxincrs],
index=[dt(x[0]) forxincrs],
columns=['Seats', 'Wait Seats']
 )
st.line_chart(df)

else:
'CRN not found! Available CRN:'
# ', '.join([str(x[0]) for x in get_available_crn(c)])
df=pd.DataFrame(get_available_crn(c))
df

'''
 ### That's it for now!

 Try changing the term above, or check back again later!

 Or, head over to [opencourse.dev](https://opencourse.dev).
 '''


# Owl API (playground, etc.)
ifpage=='API':
'''
 # Owl API [deprecated]
 ### More at [opencourse.dev:3000](https://opencourse.dev:3000)

 ## Playground
 '''

PREFIX='https://opencourse.dev:3000'
endpoint=st.text_input('Enter an API url:', value='/fh/single?dept=CS&course=1A')

response=requests.get(PREFIX+endpoint)
response.raise_for_status()

try:
json=response.json()
st.json(response.json())
except:
st.text(response.text)

df=pd.DataFrame(sum(list(dict(json).values()), []))
df


# Quickstart page (guides for buliding your own analysis)
ifpage=='Quickstart':
'''
 # Quickstart
 ## Build Your Own

 Interested in building your own analysis? We are too!
 Get started by opening `app.py` in a code editor, and try adding some stuff.
 For example:
 '''

withst.echo():
# Title and text
st.subheader('Me vs. Others')
st.write('This is a random but cool graph about me! What do you think?')

# Line chart
df=pd.DataFrame(
 [(i, pow(1.5, i)) foriinrange(11)],
columns=['People', 'Me']
 )
st.line_chart(df)


# About page
ifpage=='About':
text='''
 <style>
 .footer {
 color: #999;
 }
 .footer a {
 color: #4e8dc7 !important;
 }
 </style>

 # Open Course API

 <img src="https://avatars0.githubusercontent.com/u/40309595?s=200&v=4" width="80px" />

 ####

 Website: https://opencourse.dev/

 GitHub: https://github.com/OpenCourseAPI/

 ####

 <span class="footer">Made with ❤️ by [Madhav Varshney](https://github.com/madhavarshney)</span>
 '''
st.markdown(text, unsafe_allow_html=True)
### [![GitHub Logo](https://github.githubassets.com/images/modules/logos_page/GitHub-Mark.png)](https://github.com/OpenCourseAPI/)
####
# pylint: disable=E1120
# See: https://github.com/streamlit/streamlit/issues/1536
# See: https://discuss.streamlit.io/t/error-no-value-for-argument-body-in-method-call-vs-code/1851/6

importos
importtime
importjson
importsqlite3
fromdatetimeimportdatetime

importstreamlitasst
importpandasaspd
importnumpyasnp
importaltairasalt
importrequests

fromgenerate_dbimportGitHistoryConverter
fromsettingsimportConfig, TERM_CODES_TO_CONFIG

#
# Data and term configuration
#

ROOT='./'
TERM_CODES= {
'Foothill, Winter 2021' : '202131',
'De Anza, Winter 2021' : '202132',
'Foothill, Fall 2020' : '202121',
'De Anza, Fall 2020' : '202122',
'Foothill, Summer 2020' : '202111',
'De Anza, Summer 2020' : '202112',
}

# Workaround for a timezone bug in streamlit
# ORIGINAL_TIMEZONE = time.tzname[time.daylight]
# # os.environ['ORIG_TZ'] = time.tzname[time.daylight]
# try:
# ORIGINAL_TIMEZONE = time.tzname[1]
# # os.environ['ORIG_TZ']
# except IndexError:
# ORIGINAL_TIMEZONE = time.tzname[0]

# print(ORIGINAL_TIMEZONE)

# https://github.com/streamlit/streamlit/issues/1061#issuecomment-642057212
os.environ['TZ'] ='UTC'

#
# Data generator
#

progress_bar=None

defsetup_progress_bar(name):
globalprogress_bar

st.sidebar.text(name)
progress_bar=st.sidebar.progress(0)

defupdate_progress_bar(value):
progress_bar.progress(value)

defgenerate_data(settings, interval, full_reset):
st.sidebar.markdown('### Generating Data')
delos.environ['TZ']
# os.environ['TZ'] = ORIGINAL_TIMEZONE
GitHistoryConverter(
settings,
Config(interval, full_reset),
update_progress=update_progress_bar,
setup_progress=setup_progress_bar
 ).convert()
os.environ['TZ'] ='UTC'
st.caching.clear_cache()
st.balloons()

#
# Database Utilities
#

@st.cache(allow_output_mutation=True)
defconnect_db(term):
# TODO: check thread safety
conn=sqlite3.connect(f'{ROOT}db/temp_{term}.sqlite3', check_same_thread=False)
c=conn.cursor()
returnc

@st.cache(hash_funcs={sqlite3.Cursor:id})
defdata_exists(c):
tableExists=c.execute(
'SELECT name FROM sqlite_master WHERE type="table" AND name="classes"'
 ).fetchone()
iftableExistsandtableExists[0]:
returnTrue
returnFalse

#
# Queries
#

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_all_classes(c):
returnc.execute('''
 SELECT
 time,
 SUM(seats),
 SUM(wait_seats),
 COUNT(case status when "Open" then 1 else null end) as open_classes,
 COUNT(case status when "Waitlist" then 1 else null end) as waitlist_classes,
 COUNT(case status when "Full" then 1 else null end) as full_classes
 FROM classes
 GROUP BY time;
 ''').fetchall()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_total_classes(c):
returnc.execute('''
 SELECT
 time,
 COUNT(CRN)
 FROM classes
 GROUP BY time;
 ''').fetchall()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defcount_times(c):
returnc.execute('SELECT COUNT(time) FROM classes;').fetchone()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_available_crn(c):
returnc.execute('SELECT DISTINCT CRN FROM classes;').fetchall()

# TODO: hash
# NOTE: this does not currently work beause we do not store dept, course, and section
defget_one_class(c, dept, course, section):
returnc.execute('SELECT * FROM classes WHERE dept = ? and course = ? and section = ?', [dept, course, section]).fetchall()

# TODO: hash
defget_one_class_by_crn(c, crn):
returnc.execute('SELECT * FROM classes WHERE CRN = ?', [crn]).fetchall()

#
# Utilities
#

# Create a date from an SQLite datetime string
defdt(date_string):
returndatetime.strptime(date_string, '%Y-%m-%d %H:%M:%S')

#
# Streamlit App
#

# Sidebar page navigation
st.sidebar.markdown('## NAVIGATION')
page=st.sidebar.radio('Select a page:', ['Home', 'API', 'Quickstart', 'About'])

# Home page, with all its graphs
ifpage=='Home':
'''
 # Data Analysis
 ### By [Open Course API](https://github.com/OpenCourseAPI/)
 ####
 Tip: Use the sidebar to change the term and control other settings.
 '''

# Sidebar settings
st.sidebar.markdown('## SETTINGS')
term=st.sidebar.selectbox(
'Choose a term: ', list(TERM_CODES.keys())
 )
term_config=TERM_CODES_TO_CONFIG[TERM_CODES[term]]
# show_debug_info = st.sidebar.checkbox('Show debug information')
show_advanced_options=st.sidebar.checkbox('Show advanced options')

# Data generation options
st.sidebar.markdown('## DATA')

interval=60
ifshow_advanced_options:
interval=st.sidebar.slider('Interval time (in minutes)', value=60, min_value=5, max_value=60, step=5)

ifst.sidebar.button('Update'):
generate_data(term_config, interval, False)

ifst.sidebar.button('Regenerate'):
generate_data(term_config, interval, True)

# Main Page Content

# Load database
c=connect_db(TERM_CODES[term])

# Generate data if necessary
ifnotdata_exists(c):
generate_data(term_config, interval, True)

# Get all class data aggregated by time
class_data=get_all_classes(c)
times= [dt(x[0]) forxinclass_data]
# total_times = countTimes(c)[0]

l=len(class_data)
start_date=landclass_data[0][0]
end_index= (l-1) or0
end_date=landclass_data[end_index][0]

f'''
 ## {term}

 Selected term data info:

 | Term | (selected term) |
 | --- | --- |
 | Start | {dt(start_date).strftime('%b %d, %Y %I:%M %p')} |
 | End | {dt(end_date).strftime('%b %d, %Y %I:%M %p')} |

 '''

'''
 ## All Classes
 ### Raw Data
 '''

df=pd.DataFrame(
 [x[1:] forxinclass_data],
index=times,
columns=['Seats', 'Wait Seats', 'Open Classes', 'Waitlist', 'Full']
 )
df

'''
 ### Open Seats By Time
 '''

df=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=times,
columns=['Seats', 'Wait Seats']
 )
st.line_chart(df)

df2=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=pd.DatetimeIndex(times),
columns=['Seats', 'Wait Seats']
# )
 ).resample('D').mean()
df=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=pd.DatetimeIndex(times),
columns=['Seats', 'Wait Seats']
# ).shift()
 ).resample('D').mean().shift()
st.line_chart(df.subtract(df2))

'''
 ### Class Status By Time
 '''

df=pd.DataFrame(
 [x[3:6] forxinclass_data],
index=times,
columns=['Open', 'Waitlist', 'Full']
 )
st.area_chart(df)

'''
 ### Total Class Count By Time
 '''

total_classes=get_total_classes(c)
df=pd.DataFrame(
 [x[1:] forxintotal_classes],
index=[dt(x[0]) forxintotal_classes],
columns=['Classes']
 )
st.line_chart(df)

'''
 ### View Course History
 '''

# dept = st.text_input('Department', 'CS')
# course = st.text_input('Course', '1A')
# section = st.text_input('Section', '1Z')
# crs = get_one_class(c, dept, course, section)

crn=st.text_input('Enter a CRN', 10152)
crs=get_one_class_by_crn(c, crn)

ifcrsandlen(crs) >0:
df=pd.DataFrame(
 [x[3:5] forxincrs],
index=[dt(x[0]) forxincrs],
columns=['Seats', 'Wait Seats']
 )
st.line_chart(df)

else:
'CRN not found! Available CRN:'
# ', '.join([str(x[0]) for x in get_available_crn(c)])
df=pd.DataFrame(get_available_crn(c))
df

'''
 ### That's it for now!

 Try changing the term above, or check back again later!

 Or, head over to [opencourse.dev](https://opencourse.dev).
 '''


# Owl API (playground, etc.)
ifpage=='API':
'''
 # Owl API [deprecated]
 ### More at [opencourse.dev:3000](https://opencourse.dev:3000)

 ## Playground
 '''

PREFIX='https://opencourse.dev:3000'
endpoint=st.text_input('Enter an API url:', value='/fh/single?dept=CS&course=1A')

response=requests.get(PREFIX+endpoint)
response.raise_for_status()

try:
json=response.json()
st.json(response.json())
except:
st.text(response.text)

df=pd.DataFrame(sum(list(dict(json).values()), []))
df


# Quickstart page (guides for buliding your own analysis)
ifpage=='Quickstart':
'''
 # Quickstart
 ## Build Your Own

 Interested in building your own analysis? We are too!
 Get started by opening `app.py` in a code editor, and try adding some stuff.
 For example:
 '''

withst.echo():
# Title and text
st.subheader('Me vs. Others')
st.write('This is a random but cool graph about me! What do you think?')

# Line chart
df=pd.DataFrame(
 [(i, pow(1.5, i)) foriinrange(11)],
columns=['People', 'Me']
 )
st.line_chart(df)


# About page
ifpage=='About':
text='''
 <style>
 .footer {
 color: #999;
 }
 .footer a {
 color: #4e8dc7 !important;
 }
 </style>

 # Open Course API

 <img src="https://avatars0.githubusercontent.com/u/40309595?s=200&v=4" width="80px" />

 ####

 Website: https://opencourse.dev/

 GitHub: https://github.com/OpenCourseAPI/

 ####

 <span class="footer">Made with ❤️ by [Madhav Varshney](https://github.com/madhavarshney)</span>
 '''
st.markdown(text, unsafe_allow_html=True)
### [![GitHub Logo](https://github.githubassets.com/images/modules/logos_page/GitHub-Mark.png)](https://github.com/OpenCourseAPI/)
####
# pylint: disable=E1120
# See: https://github.com/streamlit/streamlit/issues/1536
# See: https://discuss.streamlit.io/t/error-no-value-for-argument-body-in-method-call-vs-code/1851/6

importos
importtime
importjson
importsqlite3
fromdatetimeimportdatetime

importstreamlitasst
importpandasaspd
importnumpyasnp
importaltairasalt
importrequests

fromgenerate_dbimportGitHistoryConverter
fromsettingsimportConfig, TERM_CODES_TO_CONFIG

#
# Data and term configuration
#

ROOT='./'
TERM_CODES= {
'Foothill, Winter 2021' : '202131',
'De Anza, Winter 2021' : '202132',
'Foothill, Fall 2020' : '202121',
'De Anza, Fall 2020' : '202122',
'Foothill, Summer 2020' : '202111',
'De Anza, Summer 2020' : '202112',
}

# Workaround for a timezone bug in streamlit
# ORIGINAL_TIMEZONE = time.tzname[time.daylight]
# # os.environ['ORIG_TZ'] = time.tzname[time.daylight]
# try:
# ORIGINAL_TIMEZONE = time.tzname[1]
# # os.environ['ORIG_TZ']
# except IndexError:
# ORIGINAL_TIMEZONE = time.tzname[0]

# print(ORIGINAL_TIMEZONE)

# https://github.com/streamlit/streamlit/issues/1061#issuecomment-642057212
os.environ['TZ'] ='UTC'

#
# Data generator
#

progress_bar=None

defsetup_progress_bar(name):
globalprogress_bar

st.sidebar.text(name)
progress_bar=st.sidebar.progress(0)

defupdate_progress_bar(value):
progress_bar.progress(value)

defgenerate_data(settings, interval, full_reset):
st.sidebar.markdown('### Generating Data')
delos.environ['TZ']
# os.environ['TZ'] = ORIGINAL_TIMEZONE
GitHistoryConverter(
settings,
Config(interval, full_reset),
update_progress=update_progress_bar,
setup_progress=setup_progress_bar
 ).convert()
os.environ['TZ'] ='UTC'
st.caching.clear_cache()
st.balloons()

#
# Database Utilities
#

@st.cache(allow_output_mutation=True)
defconnect_db(term):
# TODO: check thread safety
conn=sqlite3.connect(f'{ROOT}db/temp_{term}.sqlite3', check_same_thread=False)
c=conn.cursor()
returnc

@st.cache(hash_funcs={sqlite3.Cursor:id})
defdata_exists(c):
tableExists=c.execute(
'SELECT name FROM sqlite_master WHERE type="table" AND name="classes"'
 ).fetchone()
iftableExistsandtableExists[0]:
returnTrue
returnFalse

#
# Queries
#

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_all_classes(c):
returnc.execute('''
 SELECT
 time,
 SUM(seats),
 SUM(wait_seats),
 COUNT(case status when "Open" then 1 else null end) as open_classes,
 COUNT(case status when "Waitlist" then 1 else null end) as waitlist_classes,
 COUNT(case status when "Full" then 1 else null end) as full_classes
 FROM classes
 GROUP BY time;
 ''').fetchall()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_total_classes(c):
returnc.execute('''
 SELECT
 time,
 COUNT(CRN)
 FROM classes
 GROUP BY time;
 ''').fetchall()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defcount_times(c):
returnc.execute('SELECT COUNT(time) FROM classes;').fetchone()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_available_crn(c):
returnc.execute('SELECT DISTINCT CRN FROM classes;').fetchall()

# TODO: hash
# NOTE: this does not currently work beause we do not store dept, course, and section
defget_one_class(c, dept, course, section):
returnc.execute('SELECT * FROM classes WHERE dept = ? and course = ? and section = ?', [dept, course, section]).fetchall()

# TODO: hash
defget_one_class_by_crn(c, crn):
returnc.execute('SELECT * FROM classes WHERE CRN = ?', [crn]).fetchall()

#
# Utilities
#

# Create a date from an SQLite datetime string
defdt(date_string):
returndatetime.strptime(date_string, '%Y-%m-%d %H:%M:%S')

#
# Streamlit App
#

# Sidebar page navigation
st.sidebar.markdown('## NAVIGATION')
page=st.sidebar.radio('Select a page:', ['Home', 'API', 'Quickstart', 'About'])

# Home page, with all its graphs
ifpage=='Home':
'''
 # Data Analysis
 ### By [Open Course API](https://github.com/OpenCourseAPI/)
 ####
 Tip: Use the sidebar to change the term and control other settings.
 '''

# Sidebar settings
st.sidebar.markdown('## SETTINGS')
term=st.sidebar.selectbox(
'Choose a term: ', list(TERM_CODES.keys())
 )
term_config=TERM_CODES_TO_CONFIG[TERM_CODES[term]]
# show_debug_info = st.sidebar.checkbox('Show debug information')
show_advanced_options=st.sidebar.checkbox('Show advanced options')

# Data generation options
st.sidebar.markdown('## DATA')

interval=60
ifshow_advanced_options:
interval=st.sidebar.slider('Interval time (in minutes)', value=60, min_value=5, max_value=60, step=5)

ifst.sidebar.button('Update'):
generate_data(term_config, interval, False)

ifst.sidebar.button('Regenerate'):
generate_data(term_config, interval, True)

# Main Page Content

# Load database
c=connect_db(TERM_CODES[term])

# Generate data if necessary
ifnotdata_exists(c):
generate_data(term_config, interval, True)

# Get all class data aggregated by time
class_data=get_all_classes(c)
times= [dt(x[0]) forxinclass_data]
# total_times = countTimes(c)[0]

l=len(class_data)
start_date=landclass_data[0][0]
end_index= (l-1) or0
end_date=landclass_data[end_index][0]

f'''
 ## {term}

 Selected term data info:

 | Term | (selected term) |
 | --- | --- |
 | Start | {dt(start_date).strftime('%b %d, %Y %I:%M %p')} |
 | End | {dt(end_date).strftime('%b %d, %Y %I:%M %p')} |

 '''

'''
 ## All Classes
 ### Raw Data
 '''

df=pd.DataFrame(
 [x[1:] forxinclass_data],
index=times,
columns=['Seats', 'Wait Seats', 'Open Classes', 'Waitlist', 'Full']
 )
df

'''
 ### Open Seats By Time
 '''

df=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=times,
columns=['Seats', 'Wait Seats']
 )
st.line_chart(df)

df2=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=pd.DatetimeIndex(times),
columns=['Seats', 'Wait Seats']
# )
 ).resample('D').mean()
df=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=pd.DatetimeIndex(times),
columns=['Seats', 'Wait Seats']
# ).shift()
 ).resample('D').mean().shift()
st.line_chart(df.subtract(df2))

'''
 ### Class Status By Time
 '''

df=pd.DataFrame(
 [x[3:6] forxinclass_data],
index=times,
columns=['Open', 'Waitlist', 'Full']
 )
st.area_chart(df)

'''
 ### Total Class Count By Time
 '''

total_classes=get_total_classes(c)
df=pd.DataFrame(
 [x[1:] forxintotal_classes],
index=[dt(x[0]) forxintotal_classes],
columns=['Classes']
 )
st.line_chart(df)

'''
 ### View Course History
 '''

# dept = st.text_input('Department', 'CS')
# course = st.text_input('Course', '1A')
# section = st.text_input('Section', '1Z')
# crs = get_one_class(c, dept, course, section)

crn=st.text_input('Enter a CRN', 10152)
crs=get_one_class_by_crn(c, crn)

ifcrsandlen(crs) >0:
df=pd.DataFrame(
 [x[3:5] forxincrs],
index=[dt(x[0]) forxincrs],
columns=['Seats', 'Wait Seats']
 )
st.line_chart(df)

else:
'CRN not found! Available CRN:'
# ', '.join([str(x[0]) for x in get_available_crn(c)])
df=pd.DataFrame(get_available_crn(c))
df

'''
 ### That's it for now!

 Try changing the term above, or check back again later!

 Or, head over to [opencourse.dev](https://opencourse.dev).
 '''


# Owl API (playground, etc.)
ifpage=='API':
'''
 # Owl API [deprecated]
 ### More at [opencourse.dev:3000](https://opencourse.dev:3000)

 ## Playground
 '''

PREFIX='https://opencourse.dev:3000'
endpoint=st.text_input('Enter an API url:', value='/fh/single?dept=CS&course=1A')

response=requests.get(PREFIX+endpoint)
response.raise_for_status()

try:
json=response.json()
st.json(response.json())
except:
st.text(response.text)

df=pd.DataFrame(sum(list(dict(json).values()), []))
df


# Quickstart page (guides for buliding your own analysis)
ifpage=='Quickstart':
'''
 # Quickstart
 ## Build Your Own

 Interested in building your own analysis? We are too!
 Get started by opening `app.py` in a code editor, and try adding some stuff.
 For example:
 '''

withst.echo():
# Title and text
st.subheader('Me vs. Others')
st.write('This is a random but cool graph about me! What do you think?')

# Line chart
df=pd.DataFrame(
 [(i, pow(1.5, i)) foriinrange(11)],
columns=['People', 'Me']
 )
st.line_chart(df)


# About page
ifpage=='About':
text='''
 <style>
 .footer {
 color: #999;
 }
 .footer a {
 color: #4e8dc7 !important;
 }
 </style>

 # Open Course API

 <img src="https://avatars0.githubusercontent.com/u/40309595?s=200&v=4" width="80px" />

 ####

 Website: https://opencourse.dev/

 GitHub: https://github.com/OpenCourseAPI/

 ####

 <span class="footer">Made with ❤️ by [Madhav Varshney](https://github.com/madhavarshney)</span>
 '''
st.markdown(text, unsafe_allow_html=True)
### [![GitHub Logo](https://github.githubassets.com/images/modules/logos_page/GitHub-Mark.png)](https://github.com/OpenCourseAPI/)
####
# pylint: disable=E1120
# See: https://github.com/streamlit/streamlit/issues/1536
# See: https://discuss.streamlit.io/t/error-no-value-for-argument-body-in-method-call-vs-code/1851/6

importos
importtime
importjson
importsqlite3
fromdatetimeimportdatetime

importstreamlitasst
importpandasaspd
importnumpyasnp
importaltairasalt
importrequests

fromgenerate_dbimportGitHistoryConverter
fromsettingsimportConfig, TERM_CODES_TO_CONFIG

#
# Data and term configuration
#

ROOT='./'
TERM_CODES= {
'Foothill, Winter 2021' : '202131',
'De Anza, Winter 2021' : '202132',
'Foothill, Fall 2020' : '202121',
'De Anza, Fall 2020' : '202122',
'Foothill, Summer 2020' : '202111',
'De Anza, Summer 2020' : '202112',
}

# Workaround for a timezone bug in streamlit
# ORIGINAL_TIMEZONE = time.tzname[time.daylight]
# # os.environ['ORIG_TZ'] = time.tzname[time.daylight]
# try:
# ORIGINAL_TIMEZONE = time.tzname[1]
# # os.environ['ORIG_TZ']
# except IndexError:
# ORIGINAL_TIMEZONE = time.tzname[0]

# print(ORIGINAL_TIMEZONE)

# https://github.com/streamlit/streamlit/issues/1061#issuecomment-642057212
os.environ['TZ'] ='UTC'

#
# Data generator
#

progress_bar=None

defsetup_progress_bar(name):
globalprogress_bar

st.sidebar.text(name)
progress_bar=st.sidebar.progress(0)

defupdate_progress_bar(value):
progress_bar.progress(value)

defgenerate_data(settings, interval, full_reset):
st.sidebar.markdown('### Generating Data')
delos.environ['TZ']
# os.environ['TZ'] = ORIGINAL_TIMEZONE
GitHistoryConverter(
settings,
Config(interval, full_reset),
update_progress=update_progress_bar,
setup_progress=setup_progress_bar
 ).convert()
os.environ['TZ'] ='UTC'
st.caching.clear_cache()
st.balloons()

#
# Database Utilities
#

@st.cache(allow_output_mutation=True)
defconnect_db(term):
# TODO: check thread safety
conn=sqlite3.connect(f'{ROOT}db/temp_{term}.sqlite3', check_same_thread=False)
c=conn.cursor()
returnc

@st.cache(hash_funcs={sqlite3.Cursor:id})
defdata_exists(c):
tableExists=c.execute(
'SELECT name FROM sqlite_master WHERE type="table" AND name="classes"'
 ).fetchone()
iftableExistsandtableExists[0]:
returnTrue
returnFalse

#
# Queries
#

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_all_classes(c):
returnc.execute('''
 SELECT
 time,
 SUM(seats),
 SUM(wait_seats),
 COUNT(case status when "Open" then 1 else null end) as open_classes,
 COUNT(case status when "Waitlist" then 1 else null end) as waitlist_classes,
 COUNT(case status when "Full" then 1 else null end) as full_classes
 FROM classes
 GROUP BY time;
 ''').fetchall()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_total_classes(c):
returnc.execute('''
 SELECT
 time,
 COUNT(CRN)
 FROM classes
 GROUP BY time;
 ''').fetchall()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defcount_times(c):
returnc.execute('SELECT COUNT(time) FROM classes;').fetchone()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_available_crn(c):
returnc.execute('SELECT DISTINCT CRN FROM classes;').fetchall()

# TODO: hash
# NOTE: this does not currently work beause we do not store dept, course, and section
defget_one_class(c, dept, course, section):
returnc.execute('SELECT * FROM classes WHERE dept = ? and course = ? and section = ?', [dept, course, section]).fetchall()

# TODO: hash
defget_one_class_by_crn(c, crn):
returnc.execute('SELECT * FROM classes WHERE CRN = ?', [crn]).fetchall()

#
# Utilities
#

# Create a date from an SQLite datetime string
defdt(date_string):
returndatetime.strptime(date_string, '%Y-%m-%d %H:%M:%S')

#
# Streamlit App
#

# Sidebar page navigation
st.sidebar.markdown('## NAVIGATION')
page=st.sidebar.radio('Select a page:', ['Home', 'API', 'Quickstart', 'About'])

# Home page, with all its graphs
ifpage=='Home':
'''
 # Data Analysis
 ### By [Open Course API](https://github.com/OpenCourseAPI/)
 ####
 Tip: Use the sidebar to change the term and control other settings.
 '''

# Sidebar settings
st.sidebar.markdown('## SETTINGS')
term=st.sidebar.selectbox(
'Choose a term: ', list(TERM_CODES.keys())
 )
term_config=TERM_CODES_TO_CONFIG[TERM_CODES[term]]
# show_debug_info = st.sidebar.checkbox('Show debug information')
show_advanced_options=st.sidebar.checkbox('Show advanced options')

# Data generation options
st.sidebar.markdown('## DATA')

interval=60
ifshow_advanced_options:
interval=st.sidebar.slider('Interval time (in minutes)', value=60, min_value=5, max_value=60, step=5)

ifst.sidebar.button('Update'):
generate_data(term_config, interval, False)

ifst.sidebar.button('Regenerate'):
generate_data(term_config, interval, True)

# Main Page Content

# Load database
c=connect_db(TERM_CODES[term])

# Generate data if necessary
ifnotdata_exists(c):
generate_data(term_config, interval, True)

# Get all class data aggregated by time
class_data=get_all_classes(c)
times= [dt(x[0]) forxinclass_data]
# total_times = countTimes(c)[0]

l=len(class_data)
start_date=landclass_data[0][0]
end_index= (l-1) or0
end_date=landclass_data[end_index][0]

f'''
 ## {term}

 Selected term data info:

 | Term | (selected term) |
 | --- | --- |
 | Start | {dt(start_date).strftime('%b %d, %Y %I:%M %p')} |
 | End | {dt(end_date).strftime('%b %d, %Y %I:%M %p')} |

 '''

'''
 ## All Classes
 ### Raw Data
 '''

df=pd.DataFrame(
 [x[1:] forxinclass_data],
index=times,
columns=['Seats', 'Wait Seats', 'Open Classes', 'Waitlist', 'Full']
 )
df

'''
 ### Open Seats By Time
 '''

df=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=times,
columns=['Seats', 'Wait Seats']
 )
st.line_chart(df)

df2=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=pd.DatetimeIndex(times),
columns=['Seats', 'Wait Seats']
# )
 ).resample('D').mean()
df=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=pd.DatetimeIndex(times),
columns=['Seats', 'Wait Seats']
# ).shift()
 ).resample('D').mean().shift()
st.line_chart(df.subtract(df2))

'''
 ### Class Status By Time
 '''

df=pd.DataFrame(
 [x[3:6] forxinclass_data],
index=times,
columns=['Open', 'Waitlist', 'Full']
 )
st.area_chart(df)

'''
 ### Total Class Count By Time
 '''

total_classes=get_total_classes(c)
df=pd.DataFrame(
 [x[1:] forxintotal_classes],
index=[dt(x[0]) forxintotal_classes],
columns=['Classes']
 )
st.line_chart(df)

'''
 ### View Course History
 '''

# dept = st.text_input('Department', 'CS')
# course = st.text_input('Course', '1A')
# section = st.text_input('Section', '1Z')
# crs = get_one_class(c, dept, course, section)

crn=st.text_input('Enter a CRN', 10152)
crs=get_one_class_by_crn(c, crn)

ifcrsandlen(crs) >0:
df=pd.DataFrame(
 [x[3:5] forxincrs],
index=[dt(x[0]) forxincrs],
columns=['Seats', 'Wait Seats']
 )
st.line_chart(df)

else:
'CRN not found! Available CRN:'
# ', '.join([str(x[0]) for x in get_available_crn(c)])
df=pd.DataFrame(get_available_crn(c))
df

'''
 ### That's it for now!

 Try changing the term above, or check back again later!

 Or, head over to [opencourse.dev](https://opencourse.dev).
 '''


# Owl API (playground, etc.)
ifpage=='API':
'''
 # Owl API [deprecated]
 ### More at [opencourse.dev:3000](https://opencourse.dev:3000)

 ## Playground
 '''

PREFIX='https://opencourse.dev:3000'
endpoint=st.text_input('Enter an API url:', value='/fh/single?dept=CS&course=1A')

response=requests.get(PREFIX+endpoint)
response.raise_for_status()

try:
json=response.json()
st.json(response.json())
except:
st.text(response.text)

df=pd.DataFrame(sum(list(dict(json).values()), []))
df


# Quickstart page (guides for buliding your own analysis)
ifpage=='Quickstart':
'''
 # Quickstart
 ## Build Your Own

 Interested in building your own analysis? We are too!
 Get started by opening `app.py` in a code editor, and try adding some stuff.
 For example:
 '''

withst.echo():
# Title and text
st.subheader('Me vs. Others')
st.write('This is a random but cool graph about me! What do you think?')

# Line chart
df=pd.DataFrame(
 [(i, pow(1.5, i)) foriinrange(11)],
columns=['People', 'Me']
 )
st.line_chart(df)


# About page
ifpage=='About':
text='''
 <style>
 .footer {
 color: #999;
 }
 .footer a {
 color: #4e8dc7 !important;
 }
 </style>

 # Open Course API

 <img src="https://avatars0.githubusercontent.com/u/40309595?s=200&v=4" width="80px" />

 ####

 Website: https://opencourse.dev/

 GitHub: https://github.com/OpenCourseAPI/

 ####

 <span class="footer">Made with ❤️ by [Madhav Varshney](https://github.com/madhavarshney)</span>
 '''
st.markdown(text, unsafe_allow_html=True)
### [![GitHub Logo](https://github.githubassets.com/images/modules/logos_page/GitHub-Mark.png)](https://github.com/OpenCourseAPI/)
####
# pylint: disable=E1120
# See: https://github.com/streamlit/streamlit/issues/1536
# See: https://discuss.streamlit.io/t/error-no-value-for-argument-body-in-method-call-vs-code/1851/6

importos
importtime
importjson
importsqlite3
fromdatetimeimportdatetime

importstreamlitasst
importpandasaspd
importnumpyasnp
importaltairasalt
importrequests

fromgenerate_dbimportGitHistoryConverter
fromsettingsimportConfig, TERM_CODES_TO_CONFIG

#
# Data and term configuration
#

ROOT='./'
TERM_CODES= {
'Foothill, Winter 2021' : '202131',
'De Anza, Winter 2021' : '202132',
'Foothill, Fall 2020' : '202121',
'De Anza, Fall 2020' : '202122',
'Foothill, Summer 2020' : '202111',
'De Anza, Summer 2020' : '202112',
}

# Workaround for a timezone bug in streamlit
# ORIGINAL_TIMEZONE = time.tzname[time.daylight]
# # os.environ['ORIG_TZ'] = time.tzname[time.daylight]
# try:
# ORIGINAL_TIMEZONE = time.tzname[1]
# # os.environ['ORIG_TZ']
# except IndexError:
# ORIGINAL_TIMEZONE = time.tzname[0]

# print(ORIGINAL_TIMEZONE)

# https://github.com/streamlit/streamlit/issues/1061#issuecomment-642057212
os.environ['TZ'] ='UTC'

#
# Data generator
#

progress_bar=None

defsetup_progress_bar(name):
globalprogress_bar

st.sidebar.text(name)
progress_bar=st.sidebar.progress(0)

defupdate_progress_bar(value):
progress_bar.progress(value)

defgenerate_data(settings, interval, full_reset):
st.sidebar.markdown('### Generating Data')
delos.environ['TZ']
# os.environ['TZ'] = ORIGINAL_TIMEZONE
GitHistoryConverter(
settings,
Config(interval, full_reset),
update_progress=update_progress_bar,
setup_progress=setup_progress_bar
 ).convert()
os.environ['TZ'] ='UTC'
st.caching.clear_cache()
st.balloons()

#
# Database Utilities
#

@st.cache(allow_output_mutation=True)
defconnect_db(term):
# TODO: check thread safety
conn=sqlite3.connect(f'{ROOT}db/temp_{term}.sqlite3', check_same_thread=False)
c=conn.cursor()
returnc

@st.cache(hash_funcs={sqlite3.Cursor:id})
defdata_exists(c):
tableExists=c.execute(
'SELECT name FROM sqlite_master WHERE type="table" AND name="classes"'
 ).fetchone()
iftableExistsandtableExists[0]:
returnTrue
returnFalse

#
# Queries
#

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_all_classes(c):
returnc.execute('''
 SELECT
 time,
 SUM(seats),
 SUM(wait_seats),
 COUNT(case status when "Open" then 1 else null end) as open_classes,
 COUNT(case status when "Waitlist" then 1 else null end) as waitlist_classes,
 COUNT(case status when "Full" then 1 else null end) as full_classes
 FROM classes
 GROUP BY time;
 ''').fetchall()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_total_classes(c):
returnc.execute('''
 SELECT
 time,
 COUNT(CRN)
 FROM classes
 GROUP BY time;
 ''').fetchall()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defcount_times(c):
returnc.execute('SELECT COUNT(time) FROM classes;').fetchone()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_available_crn(c):
returnc.execute('SELECT DISTINCT CRN FROM classes;').fetchall()

# TODO: hash
# NOTE: this does not currently work beause we do not store dept, course, and section
defget_one_class(c, dept, course, section):
returnc.execute('SELECT * FROM classes WHERE dept = ? and course = ? and section = ?', [dept, course, section]).fetchall()

# TODO: hash
defget_one_class_by_crn(c, crn):
returnc.execute('SELECT * FROM classes WHERE CRN = ?', [crn]).fetchall()

#
# Utilities
#

# Create a date from an SQLite datetime string
defdt(date_string):
returndatetime.strptime(date_string, '%Y-%m-%d %H:%M:%S')

#
# Streamlit App
#

# Sidebar page navigation
st.sidebar.markdown('## NAVIGATION')
page=st.sidebar.radio('Select a page:', ['Home', 'API', 'Quickstart', 'About'])

# Home page, with all its graphs
ifpage=='Home':
'''
 # Data Analysis
 ### By [Open Course API](https://github.com/OpenCourseAPI/)
 ####
 Tip: Use the sidebar to change the term and control other settings.
 '''

# Sidebar settings
st.sidebar.markdown('## SETTINGS')
term=st.sidebar.selectbox(
'Choose a term: ', list(TERM_CODES.keys())
 )
term_config=TERM_CODES_TO_CONFIG[TERM_CODES[term]]
# show_debug_info = st.sidebar.checkbox('Show debug information')
show_advanced_options=st.sidebar.checkbox('Show advanced options')

# Data generation options
st.sidebar.markdown('## DATA')

interval=60
ifshow_advanced_options:
interval=st.sidebar.slider('Interval time (in minutes)', value=60, min_value=5, max_value=60, step=5)

ifst.sidebar.button('Update'):
generate_data(term_config, interval, False)

ifst.sidebar.button('Regenerate'):
generate_data(term_config, interval, True)

# Main Page Content

# Load database
c=connect_db(TERM_CODES[term])

# Generate data if necessary
ifnotdata_exists(c):
generate_data(term_config, interval, True)

# Get all class data aggregated by time
class_data=get_all_classes(c)
times= [dt(x[0]) forxinclass_data]
# total_times = countTimes(c)[0]

l=len(class_data)
start_date=landclass_data[0][0]
end_index= (l-1) or0
end_date=landclass_data[end_index][0]

f'''
 ## {term}

 Selected term data info:

 | Term | (selected term) |
 | --- | --- |
 | Start | {dt(start_date).strftime('%b %d, %Y %I:%M %p')} |
 | End | {dt(end_date).strftime('%b %d, %Y %I:%M %p')} |

 '''

'''
 ## All Classes
 ### Raw Data
 '''

df=pd.DataFrame(
 [x[1:] forxinclass_data],
index=times,
columns=['Seats', 'Wait Seats', 'Open Classes', 'Waitlist', 'Full']
 )
df

'''
 ### Open Seats By Time
 '''

df=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=times,
columns=['Seats', 'Wait Seats']
 )
st.line_chart(df)

df2=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=pd.DatetimeIndex(times),
columns=['Seats', 'Wait Seats']
# )
 ).resample('D').mean()
df=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=pd.DatetimeIndex(times),
columns=['Seats', 'Wait Seats']
# ).shift()
 ).resample('D').mean().shift()
st.line_chart(df.subtract(df2))

'''
 ### Class Status By Time
 '''

df=pd.DataFrame(
 [x[3:6] forxinclass_data],
index=times,
columns=['Open', 'Waitlist', 'Full']
 )
st.area_chart(df)

'''
 ### Total Class Count By Time
 '''

total_classes=get_total_classes(c)
df=pd.DataFrame(
 [x[1:] forxintotal_classes],
index=[dt(x[0]) forxintotal_classes],
columns=['Classes']
 )
st.line_chart(df)

'''
 ### View Course History
 '''

# dept = st.text_input('Department', 'CS')
# course = st.text_input('Course', '1A')
# section = st.text_input('Section', '1Z')
# crs = get_one_class(c, dept, course, section)

crn=st.text_input('Enter a CRN', 10152)
crs=get_one_class_by_crn(c, crn)

ifcrsandlen(crs) >0:
df=pd.DataFrame(
 [x[3:5] forxincrs],
index=[dt(x[0]) forxincrs],
columns=['Seats', 'Wait Seats']
 )
st.line_chart(df)

else:
'CRN not found! Available CRN:'
# ', '.join([str(x[0]) for x in get_available_crn(c)])
df=pd.DataFrame(get_available_crn(c))
df

'''
 ### That's it for now!

 Try changing the term above, or check back again later!

 Or, head over to [opencourse.dev](https://opencourse.dev).
 '''


# Owl API (playground, etc.)
ifpage=='API':
'''
 # Owl API [deprecated]
 ### More at [opencourse.dev:3000](https://opencourse.dev:3000)

 ## Playground
 '''

PREFIX='https://opencourse.dev:3000'
endpoint=st.text_input('Enter an API url:', value='/fh/single?dept=CS&course=1A')

response=requests.get(PREFIX+endpoint)
response.raise_for_status()

try:
json=response.json()
st.json(response.json())
except:
st.text(response.text)

df=pd.DataFrame(sum(list(dict(json).values()), []))
df


# Quickstart page (guides for buliding your own analysis)
ifpage=='Quickstart':
'''
 # Quickstart
 ## Build Your Own

 Interested in building your own analysis? We are too!
 Get started by opening `app.py` in a code editor, and try adding some stuff.
 For example:
 '''

withst.echo():
# Title and text
st.subheader('Me vs. Others')
st.write('This is a random but cool graph about me! What do you think?')

# Line chart
df=pd.DataFrame(
 [(i, pow(1.5, i)) foriinrange(11)],
columns=['People', 'Me']
 )
st.line_chart(df)


# About page
ifpage=='About':
text='''
 <style>
 .footer {
 color: #999;
 }
 .footer a {
 color: #4e8dc7 !important;
 }
 </style>

 # Open Course API

 <img src="https://avatars0.githubusercontent.com/u/40309595?s=200&v=4" width="80px" />

 ####

 Website: https://opencourse.dev/

 GitHub: https://github.com/OpenCourseAPI/

 ####

 <span class="footer">Made with ❤️ by [Madhav Varshney](https://github.com/madhavarshney)</span>
 '''
st.markdown(text, unsafe_allow_html=True)
### [![GitHub Logo](https://github.githubassets.com/images/modules/logos_page/GitHub-Mark.png)](https://github.com/OpenCourseAPI/)
####
# pylint: disable=E1120
# See: https://github.com/streamlit/streamlit/issues/1536
# See: https://discuss.streamlit.io/t/error-no-value-for-argument-body-in-method-call-vs-code/1851/6

importos
importtime
importjson
importsqlite3
fromdatetimeimportdatetime

importstreamlitasst
importpandasaspd
importnumpyasnp
importaltairasalt
importrequests

fromgenerate_dbimportGitHistoryConverter
fromsettingsimportConfig, TERM_CODES_TO_CONFIG

#
# Data and term configuration
#

ROOT='./'
TERM_CODES= {
'Foothill, Winter 2021' : '202131',
'De Anza, Winter 2021' : '202132',
'Foothill, Fall 2020' : '202121',
'De Anza, Fall 2020' : '202122',
'Foothill, Summer 2020' : '202111',
'De Anza, Summer 2020' : '202112',
}

# Workaround for a timezone bug in streamlit
# ORIGINAL_TIMEZONE = time.tzname[time.daylight]
# # os.environ['ORIG_TZ'] = time.tzname[time.daylight]
# try:
# ORIGINAL_TIMEZONE = time.tzname[1]
# # os.environ['ORIG_TZ']
# except IndexError:
# ORIGINAL_TIMEZONE = time.tzname[0]

# print(ORIGINAL_TIMEZONE)

# https://github.com/streamlit/streamlit/issues/1061#issuecomment-642057212
os.environ['TZ'] ='UTC'

#
# Data generator
#

progress_bar=None

defsetup_progress_bar(name):
globalprogress_bar

st.sidebar.text(name)
progress_bar=st.sidebar.progress(0)

defupdate_progress_bar(value):
progress_bar.progress(value)

defgenerate_data(settings, interval, full_reset):
st.sidebar.markdown('### Generating Data')
delos.environ['TZ']
# os.environ['TZ'] = ORIGINAL_TIMEZONE
GitHistoryConverter(
settings,
Config(interval, full_reset),
update_progress=update_progress_bar,
setup_progress=setup_progress_bar
 ).convert()
os.environ['TZ'] ='UTC'
st.caching.clear_cache()
st.balloons()

#
# Database Utilities
#

@st.cache(allow_output_mutation=True)
defconnect_db(term):
# TODO: check thread safety
conn=sqlite3.connect(f'{ROOT}db/temp_{term}.sqlite3', check_same_thread=False)
c=conn.cursor()
returnc

@st.cache(hash_funcs={sqlite3.Cursor:id})
defdata_exists(c):
tableExists=c.execute(
'SELECT name FROM sqlite_master WHERE type="table" AND name="classes"'
 ).fetchone()
iftableExistsandtableExists[0]:
returnTrue
returnFalse

#
# Queries
#

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_all_classes(c):
returnc.execute('''
 SELECT
 time,
 SUM(seats),
 SUM(wait_seats),
 COUNT(case status when "Open" then 1 else null end) as open_classes,
 COUNT(case status when "Waitlist" then 1 else null end) as waitlist_classes,
 COUNT(case status when "Full" then 1 else null end) as full_classes
 FROM classes
 GROUP BY time;
 ''').fetchall()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_total_classes(c):
returnc.execute('''
 SELECT
 time,
 COUNT(CRN)
 FROM classes
 GROUP BY time;
 ''').fetchall()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defcount_times(c):
returnc.execute('SELECT COUNT(time) FROM classes;').fetchone()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_available_crn(c):
returnc.execute('SELECT DISTINCT CRN FROM classes;').fetchall()

# TODO: hash
# NOTE: this does not currently work beause we do not store dept, course, and section
defget_one_class(c, dept, course, section):
returnc.execute('SELECT * FROM classes WHERE dept = ? and course = ? and section = ?', [dept, course, section]).fetchall()

# TODO: hash
defget_one_class_by_crn(c, crn):
returnc.execute('SELECT * FROM classes WHERE CRN = ?', [crn]).fetchall()

#
# Utilities
#

# Create a date from an SQLite datetime string
defdt(date_string):
returndatetime.strptime(date_string, '%Y-%m-%d %H:%M:%S')

#
# Streamlit App
#

# Sidebar page navigation
st.sidebar.markdown('## NAVIGATION')
page=st.sidebar.radio('Select a page:', ['Home', 'API', 'Quickstart', 'About'])

# Home page, with all its graphs
ifpage=='Home':
'''
 # Data Analysis
 ### By [Open Course API](https://github.com/OpenCourseAPI/)
 ####
 Tip: Use the sidebar to change the term and control other settings.
 '''

# Sidebar settings
st.sidebar.markdown('## SETTINGS')
term=st.sidebar.selectbox(
'Choose a term: ', list(TERM_CODES.keys())
 )
term_config=TERM_CODES_TO_CONFIG[TERM_CODES[term]]
# show_debug_info = st.sidebar.checkbox('Show debug information')
show_advanced_options=st.sidebar.checkbox('Show advanced options')

# Data generation options
st.sidebar.markdown('## DATA')

interval=60
ifshow_advanced_options:
interval=st.sidebar.slider('Interval time (in minutes)', value=60, min_value=5, max_value=60, step=5)

ifst.sidebar.button('Update'):
generate_data(term_config, interval, False)

ifst.sidebar.button('Regenerate'):
generate_data(term_config, interval, True)

# Main Page Content

# Load database
c=connect_db(TERM_CODES[term])

# Generate data if necessary
ifnotdata_exists(c):
generate_data(term_config, interval, True)

# Get all class data aggregated by time
class_data=get_all_classes(c)
times= [dt(x[0]) forxinclass_data]
# total_times = countTimes(c)[0]

l=len(class_data)
start_date=landclass_data[0][0]
end_index= (l-1) or0
end_date=landclass_data[end_index][0]

f'''
 ## {term}

 Selected term data info:

 | Term | (selected term) |
 | --- | --- |
 | Start | {dt(start_date).strftime('%b %d, %Y %I:%M %p')} |
 | End | {dt(end_date).strftime('%b %d, %Y %I:%M %p')} |

 '''

'''
 ## All Classes
 ### Raw Data
 '''

df=pd.DataFrame(
 [x[1:] forxinclass_data],
index=times,
columns=['Seats', 'Wait Seats', 'Open Classes', 'Waitlist', 'Full']
 )
df

'''
 ### Open Seats By Time
 '''

df=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=times,
columns=['Seats', 'Wait Seats']
 )
st.line_chart(df)

df2=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=pd.DatetimeIndex(times),
columns=['Seats', 'Wait Seats']
# )
 ).resample('D').mean()
df=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=pd.DatetimeIndex(times),
columns=['Seats', 'Wait Seats']
# ).shift()
 ).resample('D').mean().shift()
st.line_chart(df.subtract(df2))

'''
 ### Class Status By Time
 '''

df=pd.DataFrame(
 [x[3:6] forxinclass_data],
index=times,
columns=['Open', 'Waitlist', 'Full']
 )
st.area_chart(df)

'''
 ### Total Class Count By Time
 '''

total_classes=get_total_classes(c)
df=pd.DataFrame(
 [x[1:] forxintotal_classes],
index=[dt(x[0]) forxintotal_classes],
columns=['Classes']
 )
st.line_chart(df)

'''
 ### View Course History
 '''

# dept = st.text_input('Department', 'CS')
# course = st.text_input('Course', '1A')
# section = st.text_input('Section', '1Z')
# crs = get_one_class(c, dept, course, section)

crn=st.text_input('Enter a CRN', 10152)
crs=get_one_class_by_crn(c, crn)

ifcrsandlen(crs) >0:
df=pd.DataFrame(
 [x[3:5] forxincrs],
index=[dt(x[0]) forxincrs],
columns=['Seats', 'Wait Seats']
 )
st.line_chart(df)

else:
'CRN not found! Available CRN:'
# ', '.join([str(x[0]) for x in get_available_crn(c)])
df=pd.DataFrame(get_available_crn(c))
df

'''
 ### That's it for now!

 Try changing the term above, or check back again later!

 Or, head over to [opencourse.dev](https://opencourse.dev).
 '''


# Owl API (playground, etc.)
ifpage=='API':
'''
 # Owl API [deprecated]
 ### More at [opencourse.dev:3000](https://opencourse.dev:3000)

 ## Playground
 '''

PREFIX='https://opencourse.dev:3000'
endpoint=st.text_input('Enter an API url:', value='/fh/single?dept=CS&course=1A')

response=requests.get(PREFIX+endpoint)
response.raise_for_status()

try:
json=response.json()
st.json(response.json())
except:
st.text(response.text)

df=pd.DataFrame(sum(list(dict(json).values()), []))
df


# Quickstart page (guides for buliding your own analysis)
ifpage=='Quickstart':
'''
 # Quickstart
 ## Build Your Own

 Interested in building your own analysis? We are too!
 Get started by opening `app.py` in a code editor, and try adding some stuff.
 For example:
 '''

withst.echo():
# Title and text
st.subheader('Me vs. Others')
st.write('This is a random but cool graph about me! What do you think?')

# Line chart
df=pd.DataFrame(
 [(i, pow(1.5, i)) foriinrange(11)],
columns=['People', 'Me']
 )
st.line_chart(df)


# About page
ifpage=='About':
text='''
 <style>
 .footer {
 color: #999;
 }
 .footer a {
 color: #4e8dc7 !important;
 }
 </style>

 # Open Course API

 <img src="https://avatars0.githubusercontent.com/u/40309595?s=200&v=4" width="80px" />

 ####

 Website: https://opencourse.dev/

 GitHub: https://github.com/OpenCourseAPI/

 ####

 <span class="footer">Made with ❤️ by [Madhav Varshney](https://github.com/madhavarshney)</span>
 '''
st.markdown(text, unsafe_allow_html=True)
### [![GitHub Logo](https://github.githubassets.com/images/modules/logos_page/GitHub-Mark.png)](https://github.com/OpenCourseAPI/)
####
# pylint: disable=E1120
# See: https://github.com/streamlit/streamlit/issues/1536
# See: https://discuss.streamlit.io/t/error-no-value-for-argument-body-in-method-call-vs-code/1851/6

importos
importtime
importjson
importsqlite3
fromdatetimeimportdatetime

importstreamlitasst
importpandasaspd
importnumpyasnp
importaltairasalt
importrequests

fromgenerate_dbimportGitHistoryConverter
fromsettingsimportConfig, TERM_CODES_TO_CONFIG

#
# Data and term configuration
#

ROOT='./'
TERM_CODES= {
'Foothill, Winter 2021' : '202131',
'De Anza, Winter 2021' : '202132',
'Foothill, Fall 2020' : '202121',
'De Anza, Fall 2020' : '202122',
'Foothill, Summer 2020' : '202111',
'De Anza, Summer 2020' : '202112',
}

# Workaround for a timezone bug in streamlit
# ORIGINAL_TIMEZONE = time.tzname[time.daylight]
# # os.environ['ORIG_TZ'] = time.tzname[time.daylight]
# try:
# ORIGINAL_TIMEZONE = time.tzname[1]
# # os.environ['ORIG_TZ']
# except IndexError:
# ORIGINAL_TIMEZONE = time.tzname[0]

# print(ORIGINAL_TIMEZONE)

# https://github.com/streamlit/streamlit/issues/1061#issuecomment-642057212
os.environ['TZ'] ='UTC'

#
# Data generator
#

progress_bar=None

defsetup_progress_bar(name):
globalprogress_bar

st.sidebar.text(name)
progress_bar=st.sidebar.progress(0)

defupdate_progress_bar(value):
progress_bar.progress(value)

defgenerate_data(settings, interval, full_reset):
st.sidebar.markdown('### Generating Data')
delos.environ['TZ']
# os.environ['TZ'] = ORIGINAL_TIMEZONE
GitHistoryConverter(
settings,
Config(interval, full_reset),
update_progress=update_progress_bar,
setup_progress=setup_progress_bar
 ).convert()
os.environ['TZ'] ='UTC'
st.caching.clear_cache()
st.balloons()

#
# Database Utilities
#

@st.cache(allow_output_mutation=True)
defconnect_db(term):
# TODO: check thread safety
conn=sqlite3.connect(f'{ROOT}db/temp_{term}.sqlite3', check_same_thread=False)
c=conn.cursor()
returnc

@st.cache(hash_funcs={sqlite3.Cursor:id})
defdata_exists(c):
tableExists=c.execute(
'SELECT name FROM sqlite_master WHERE type="table" AND name="classes"'
 ).fetchone()
iftableExistsandtableExists[0]:
returnTrue
returnFalse

#
# Queries
#

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_all_classes(c):
returnc.execute('''
 SELECT
 time,
 SUM(seats),
 SUM(wait_seats),
 COUNT(case status when "Open" then 1 else null end) as open_classes,
 COUNT(case status when "Waitlist" then 1 else null end) as waitlist_classes,
 COUNT(case status when "Full" then 1 else null end) as full_classes
 FROM classes
 GROUP BY time;
 ''').fetchall()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_total_classes(c):
returnc.execute('''
 SELECT
 time,
 COUNT(CRN)
 FROM classes
 GROUP BY time;
 ''').fetchall()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defcount_times(c):
returnc.execute('SELECT COUNT(time) FROM classes;').fetchone()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_available_crn(c):
returnc.execute('SELECT DISTINCT CRN FROM classes;').fetchall()

# TODO: hash
# NOTE: this does not currently work beause we do not store dept, course, and section
defget_one_class(c, dept, course, section):
returnc.execute('SELECT * FROM classes WHERE dept = ? and course = ? and section = ?', [dept, course, section]).fetchall()

# TODO: hash
defget_one_class_by_crn(c, crn):
returnc.execute('SELECT * FROM classes WHERE CRN = ?', [crn]).fetchall()

#
# Utilities
#

# Create a date from an SQLite datetime string
defdt(date_string):
returndatetime.strptime(date_string, '%Y-%m-%d %H:%M:%S')

#
# Streamlit App
#

# Sidebar page navigation
st.sidebar.markdown('## NAVIGATION')
page=st.sidebar.radio('Select a page:', ['Home', 'API', 'Quickstart', 'About'])

# Home page, with all its graphs
ifpage=='Home':
'''
 # Data Analysis
 ### By [Open Course API](https://github.com/OpenCourseAPI/)
 ####
 Tip: Use the sidebar to change the term and control other settings.
 '''

# Sidebar settings
st.sidebar.markdown('## SETTINGS')
term=st.sidebar.selectbox(
'Choose a term: ', list(TERM_CODES.keys())
 )
term_config=TERM_CODES_TO_CONFIG[TERM_CODES[term]]
# show_debug_info = st.sidebar.checkbox('Show debug information')
show_advanced_options=st.sidebar.checkbox('Show advanced options')

# Data generation options
st.sidebar.markdown('## DATA')

interval=60
ifshow_advanced_options:
interval=st.sidebar.slider('Interval time (in minutes)', value=60, min_value=5, max_value=60, step=5)

ifst.sidebar.button('Update'):
generate_data(term_config, interval, False)

ifst.sidebar.button('Regenerate'):
generate_data(term_config, interval, True)

# Main Page Content

# Load database
c=connect_db(TERM_CODES[term])

# Generate data if necessary
ifnotdata_exists(c):
generate_data(term_config, interval, True)

# Get all class data aggregated by time
class_data=get_all_classes(c)
times= [dt(x[0]) forxinclass_data]
# total_times = countTimes(c)[0]

l=len(class_data)
start_date=landclass_data[0][0]
end_index= (l-1) or0
end_date=landclass_data[end_index][0]

f'''
 ## {term}

 Selected term data info:

 | Term | (selected term) |
 | --- | --- |
 | Start | {dt(start_date).strftime('%b %d, %Y %I:%M %p')} |
 | End | {dt(end_date).strftime('%b %d, %Y %I:%M %p')} |

 '''

'''
 ## All Classes
 ### Raw Data
 '''

df=pd.DataFrame(
 [x[1:] forxinclass_data],
index=times,
columns=['Seats', 'Wait Seats', 'Open Classes', 'Waitlist', 'Full']
 )
df

'''
 ### Open Seats By Time
 '''

df=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=times,
columns=['Seats', 'Wait Seats']
 )
st.line_chart(df)

df2=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=pd.DatetimeIndex(times),
columns=['Seats', 'Wait Seats']
# )
 ).resample('D').mean()
df=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=pd.DatetimeIndex(times),
columns=['Seats', 'Wait Seats']
# ).shift()
 ).resample('D').mean().shift()
st.line_chart(df.subtract(df2))

'''
 ### Class Status By Time
 '''

df=pd.DataFrame(
 [x[3:6] forxinclass_data],
index=times,
columns=['Open', 'Waitlist', 'Full']
 )
st.area_chart(df)

'''
 ### Total Class Count By Time
 '''

total_classes=get_total_classes(c)
df=pd.DataFrame(
 [x[1:] forxintotal_classes],
index=[dt(x[0]) forxintotal_classes],
columns=['Classes']
 )
st.line_chart(df)

'''
 ### View Course History
 '''

# dept = st.text_input('Department', 'CS')
# course = st.text_input('Course', '1A')
# section = st.text_input('Section', '1Z')
# crs = get_one_class(c, dept, course, section)

crn=st.text_input('Enter a CRN', 10152)
crs=get_one_class_by_crn(c, crn)

ifcrsandlen(crs) >0:
df=pd.DataFrame(
 [x[3:5] forxincrs],
index=[dt(x[0]) forxincrs],
columns=['Seats', 'Wait Seats']
 )
st.line_chart(df)

else:
'CRN not found! Available CRN:'
# ', '.join([str(x[0]) for x in get_available_crn(c)])
df=pd.DataFrame(get_available_crn(c))
df

'''
 ### That's it for now!

 Try changing the term above, or check back again later!

 Or, head over to [opencourse.dev](https://opencourse.dev).
 '''


# Owl API (playground, etc.)
ifpage=='API':
'''
 # Owl API [deprecated]
 ### More at [opencourse.dev:3000](https://opencourse.dev:3000)

 ## Playground
 '''

PREFIX='https://opencourse.dev:3000'
endpoint=st.text_input('Enter an API url:', value='/fh/single?dept=CS&course=1A')

response=requests.get(PREFIX+endpoint)
response.raise_for_status()

try:
json=response.json()
st.json(response.json())
except:
st.text(response.text)

df=pd.DataFrame(sum(list(dict(json).values()), []))
df


# Quickstart page (guides for buliding your own analysis)
ifpage=='Quickstart':
'''
 # Quickstart
 ## Build Your Own

 Interested in building your own analysis? We are too!
 Get started by opening `app.py` in a code editor, and try adding some stuff.
 For example:
 '''

withst.echo():
# Title and text
st.subheader('Me vs. Others')
st.write('This is a random but cool graph about me! What do you think?')

# Line chart
df=pd.DataFrame(
 [(i, pow(1.5, i)) foriinrange(11)],
columns=['People', 'Me']
 )
st.line_chart(df)


# About page
ifpage=='About':
text='''
 <style>
 .footer {
 color: #999;
 }
 .footer a {
 color: #4e8dc7 !important;
 }
 </style>

 # Open Course API

 <img src="https://avatars0.githubusercontent.com/u/40309595?s=200&v=4" width="80px" />

 ####

 Website: https://opencourse.dev/

 GitHub: https://github.com/OpenCourseAPI/

 ####

 <span class="footer">Made with ❤️ by [Madhav Varshney](https://github.com/madhavarshney)</span>
 '''
st.markdown(text, unsafe_allow_html=True)
### [![GitHub Logo](https://github.githubassets.com/images/modules/logos_page/GitHub-Mark.png)](https://github.com/OpenCourseAPI/)
####
# pylint: disable=E1120
# See: https://github.com/streamlit/streamlit/issues/1536
# See: https://discuss.streamlit.io/t/error-no-value-for-argument-body-in-method-call-vs-code/1851/6

importos
importtime
importjson
importsqlite3
fromdatetimeimportdatetime

importstreamlitasst
importpandasaspd
importnumpyasnp
importaltairasalt
importrequests

fromgenerate_dbimportGitHistoryConverter
fromsettingsimportConfig, TERM_CODES_TO_CONFIG

#
# Data and term configuration
#

ROOT='./'
TERM_CODES= {
'Foothill, Winter 2021' : '202131',
'De Anza, Winter 2021' : '202132',
'Foothill, Fall 2020' : '202121',
'De Anza, Fall 2020' : '202122',
'Foothill, Summer 2020' : '202111',
'De Anza, Summer 2020' : '202112',
}

# Workaround for a timezone bug in streamlit
# ORIGINAL_TIMEZONE = time.tzname[time.daylight]
# # os.environ['ORIG_TZ'] = time.tzname[time.daylight]
# try:
# ORIGINAL_TIMEZONE = time.tzname[1]
# # os.environ['ORIG_TZ']
# except IndexError:
# ORIGINAL_TIMEZONE = time.tzname[0]

# print(ORIGINAL_TIMEZONE)

# https://github.com/streamlit/streamlit/issues/1061#issuecomment-642057212
os.environ['TZ'] ='UTC'

#
# Data generator
#

progress_bar=None

defsetup_progress_bar(name):
globalprogress_bar

st.sidebar.text(name)
progress_bar=st.sidebar.progress(0)

defupdate_progress_bar(value):
progress_bar.progress(value)

defgenerate_data(settings, interval, full_reset):
st.sidebar.markdown('### Generating Data')
delos.environ['TZ']
# os.environ['TZ'] = ORIGINAL_TIMEZONE
GitHistoryConverter(
settings,
Config(interval, full_reset),
update_progress=update_progress_bar,
setup_progress=setup_progress_bar
 ).convert()
os.environ['TZ'] ='UTC'
st.caching.clear_cache()
st.balloons()

#
# Database Utilities
#

@st.cache(allow_output_mutation=True)
defconnect_db(term):
# TODO: check thread safety
conn=sqlite3.connect(f'{ROOT}db/temp_{term}.sqlite3', check_same_thread=False)
c=conn.cursor()
returnc

@st.cache(hash_funcs={sqlite3.Cursor:id})
defdata_exists(c):
tableExists=c.execute(
'SELECT name FROM sqlite_master WHERE type="table" AND name="classes"'
 ).fetchone()
iftableExistsandtableExists[0]:
returnTrue
returnFalse

#
# Queries
#

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_all_classes(c):
returnc.execute('''
 SELECT
 time,
 SUM(seats),
 SUM(wait_seats),
 COUNT(case status when "Open" then 1 else null end) as open_classes,
 COUNT(case status when "Waitlist" then 1 else null end) as waitlist_classes,
 COUNT(case status when "Full" then 1 else null end) as full_classes
 FROM classes
 GROUP BY time;
 ''').fetchall()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_total_classes(c):
returnc.execute('''
 SELECT
 time,
 COUNT(CRN)
 FROM classes
 GROUP BY time;
 ''').fetchall()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defcount_times(c):
returnc.execute('SELECT COUNT(time) FROM classes;').fetchone()

@st.cache(allow_output_mutation=True, hash_funcs={sqlite3.Cursor:id})
defget_available_crn(c):
returnc.execute('SELECT DISTINCT CRN FROM classes;').fetchall()

# TODO: hash
# NOTE: this does not currently work beause we do not store dept, course, and section
defget_one_class(c, dept, course, section):
returnc.execute('SELECT * FROM classes WHERE dept = ? and course = ? and section = ?', [dept, course, section]).fetchall()

# TODO: hash
defget_one_class_by_crn(c, crn):
returnc.execute('SELECT * FROM classes WHERE CRN = ?', [crn]).fetchall()

#
# Utilities
#

# Create a date from an SQLite datetime string
defdt(date_string):
returndatetime.strptime(date_string, '%Y-%m-%d %H:%M:%S')

#
# Streamlit App
#

# Sidebar page navigation
st.sidebar.markdown('## NAVIGATION')
page=st.sidebar.radio('Select a page:', ['Home', 'API', 'Quickstart', 'About'])

# Home page, with all its graphs
ifpage=='Home':
'''
 # Data Analysis
 ### By [Open Course API](https://github.com/OpenCourseAPI/)
 ####
 Tip: Use the sidebar to change the term and control other settings.
 '''

# Sidebar settings
st.sidebar.markdown('## SETTINGS')
term=st.sidebar.selectbox(
'Choose a term: ', list(TERM_CODES.keys())
 )
term_config=TERM_CODES_TO_CONFIG[TERM_CODES[term]]
# show_debug_info = st.sidebar.checkbox('Show debug information')
show_advanced_options=st.sidebar.checkbox('Show advanced options')

# Data generation options
st.sidebar.markdown('## DATA')

interval=60
ifshow_advanced_options:
interval=st.sidebar.slider('Interval time (in minutes)', value=60, min_value=5, max_value=60, step=5)

ifst.sidebar.button('Update'):
generate_data(term_config, interval, False)

ifst.sidebar.button('Regenerate'):
generate_data(term_config, interval, True)

# Main Page Content

# Load database
c=connect_db(TERM_CODES[term])

# Generate data if necessary
ifnotdata_exists(c):
generate_data(term_config, interval, True)

# Get all class data aggregated by time
class_data=get_all_classes(c)
times= [dt(x[0]) forxinclass_data]
# total_times = countTimes(c)[0]

l=len(class_data)
start_date=landclass_data[0][0]
end_index= (l-1) or0
end_date=landclass_data[end_index][0]

f'''
 ## {term}

 Selected term data info:

 | Term | (selected term) |
 | --- | --- |
 | Start | {dt(start_date).strftime('%b %d, %Y %I:%M %p')} |
 | End | {dt(end_date).strftime('%b %d, %Y %I:%M %p')} |

 '''

'''
 ## All Classes
 ### Raw Data
 '''

df=pd.DataFrame(
 [x[1:] forxinclass_data],
index=times,
columns=['Seats', 'Wait Seats', 'Open Classes', 'Waitlist', 'Full']
 )
df

'''
 ### Open Seats By Time
 '''

df=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=times,
columns=['Seats', 'Wait Seats']
 )
st.line_chart(df)

df2=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=pd.DatetimeIndex(times),
columns=['Seats', 'Wait Seats']
# )
 ).resample('D').mean()
df=pd.DataFrame(
 [x[1:3] forxinclass_data],
index=pd.DatetimeIndex(times),
columns=['Seats', 'Wait Seats']
# ).shift()
 ).resample('D').mean().shift()
st.line_chart(df.subtract(df2))

'''
 ### Class Status By Time
 '''

df=pd.DataFrame(
 [x[3:6] forxinclass_data],
index=times,
columns=['Open', 'Waitlist', 'Full']
 )
st.area_chart(df)

'''
 ### Total Class Count By Time
 '''

total_classes=get_total_classes(c)
df=pd.DataFrame(
 [x[1:] forxintotal_classes],
index=[dt(x[0]) forxintotal_classes],
columns=['Classes']
 )
st.line_chart(df)

'''
 ### View Course History
 '''

# dept = st.text_input('Department', 'CS')
# course = st.text_input('Course', '1A')
# section = st.text_input('Section', '1Z')
# crs = get_one_class(c, dept, course, section)

crn=st.text_input('Enter a CRN', 10152)
crs=get_one_class_by_crn(c, crn)

ifcrsandlen(crs) >0:
df=pd.DataFrame(
 [x[3:5] forxincrs],
index=[dt(x[0]) forxincrs],
columns=['Seats', 'Wait Seats']
 )
st.line_chart(df)

else:
'CRN not found! Available CRN:'
# ', '.join([str(x[0]) for x in get_available_crn(c)])
df=pd.DataFrame(get_available_crn(c))
df

'''
 ### That's it for now!

 Try changing the term above, or check back again later!

 Or, head over to [opencourse.dev](https://opencourse.dev).
 '''


# Owl API (playground, etc.)
ifpage=='API':
'''
 # Owl API [deprecated]
 ### More at [opencourse.dev:3000](https://opencourse.dev:3000)

 ## Playground
 '''

PREFIX='https://opencourse.dev:3000'
endpoint=st.text_input('Enter an API url:', value='/fh/single?dept=CS&course=1A')

response=requests.get(PREFIX+endpoint)
response.raise_for_status()

try:
json=response.json()
st.json(response.json())
except:
st.text(response.text)

df=pd.DataFrame(sum(list(dict(json).values()), []))
df


# Quickstart page (guides for buliding your own analysis)
ifpage=='Quickstart':
'''
 # Quickstart
 ## Build Your Own

 Interested in building your own analysis? We are too!
 Get started by opening `app.py` in a code editor, and try adding some stuff.
 For example:
 '''

withst.echo():
# Title and text
st.subheader('Me vs. Others')
st.write('This is a random but cool graph about me! What do you think?')

# Line chart
df=pd.DataFrame(
 [(i, pow(1.5, i)) foriinrange(11)],
columns=['People', 'Me']
 )
st.line_chart(df)


# About page
ifpage=='About':
text='''
 <style>
 .footer {
 color: #999;
 }
 .footer a {
 color: #4e8dc7 !important;
 }
 </style>

 # Open Course API

 <img src="https://avatars0.githubusercontent.com/u/40309595?s=200&v=4" width="80px" />

 ####

 Website: https://opencourse.dev/

 GitHub: https://github.com/OpenCourseAPI/

 ####

 <span class="footer">Made with ❤️ by [Madhav Varshney](https://github.com/madhavarshney)</span>
 '''
st.markdown(text, unsafe_allow_html=True)
### [![GitHub Logo](https://github.githubassets.com/images/modules/logos_page/GitHub-Mark.png)](https://github.com/OpenCourseAPI/)
####