def all_plots_dashboard_dict(query_components):
conn = get_db_connection()
cursor = conn.cursor(as_dict=True)
age_group = age_group_plot_data(cursor, query_components)
gauge = random.randint(80, 90)
conn.close()
python_dict = {"age_group": age_group, "gauge": gauge}
return python_dict
def get_date_recent(): # Extract Data from Microsoft SQL Server
conn = get_db_connection()
SQL_Query = pd.read_sql_query(
'''SELECT RecencyTest_Date,COUNT(New_HIV_Diagnosis_and_Recency_Test_taken) AS Count
FROM HIV_Sample_tables
WHERE New_HIV_Diagnosis_and_Recency_Test_taken = 'Recent'
GROUP BY RecencyTest_Date;
''', conn)
d = pd.DataFrame(SQL_Query)
conn.close()
return d
def get_age_count_recent(): # Extract Data from Microsoft SQL Server
conn = get_db_connection()
SQL_Query = pd.read_sql_query(
'''SELECT Age_Group,COUNT(*) AS Count
FROM HIV_Sample_tables
GROUP BY Age_Group,New_HIV_Diagnosis_and_Recency_Test_taken
HAVING New_HIV_Diagnosis_and_Recency_Test_taken = 'Recent'
ORDER BY Age_Group;''', conn)
d = pd.DataFrame(SQL_Query)
conn.close()
return d
def get_site_count_LT(): # Extract Data from Microsoft SQL Server
conn = get_db_connection()
SQL_Query = pd.read_sql_query(
'''SELECT Site_ID,COUNT(*) AS Count
FROM HIV_Sample_tables
GROUP BY Site_ID,New_HIV_Diagnosis_and_Recency_Test_taken
HAVING New_HIV_Diagnosis_and_Recency_Test_taken = 'Long-term'
ORDER BY Site_ID;''', conn)
d = pd.DataFrame(SQL_Query)
conn.close()
return d
def get_contact_test_count(): # Extract Data from Microsoft SQL Server
conn = get_db_connection()
SQL_Query = pd.read_sql_query(
'''SELECT Index_Test,COUNT(*) AS Count
FROM HIV_Sample_tables
GROUP BY Index_Test
ORDER BY Index_Test;
''', conn)
d = pd.DataFrame(SQL_Query)
conn.close()
return d
def new_hiv_count(): # Extract Data from Microsoft SQL Server
conn = get_db_connection()
SQL_Query = pd.read_sql_query(
'''SELECT New_HIV_Diagnosis_and_Recency_Test_taken,COUNT(*) AS Count
FROM HIV_Sample_tables
GROUP BY New_HIV_Diagnosis_and_Recency_Test_taken
ORDER BY New_HIV_Diagnosis_and_Recency_Test_taken;;;;
''', conn)
d = pd.DataFrame(SQL_Query)
conn.close()
return d
def get_risk_count(): # Extract Data from Microsoft SQL Server
conn = get_db_connection()
SQL_Query = pd.read_sql_query(
'''SELECT Transmission_Category,COUNT(*) AS Count
FROM HIV_Sample_tables
GROUP BY Transmission_Category
ORDER BY Transmission_Category;;
''', conn)
d = pd.DataFrame(SQL_Query)
conn.close()
return d
def get_trgender(): # Extract Data from Microsoft SQL Server
conn = get_db_connection()
SQL_Query = pd.read_sql_query(
'''SELECT DATEPART(month, RecencyTest_Date) AS Month,COUNT(New_HIV_Diagnosis_and_Recency_Test_taken) AS Count
FROM HIV_Sample_tables
WHERE Sex = 'Trangender'
GROUP BY DATEPART(month, RecencyTest_Date)
order by Month;;
''', conn)
d = pd.DataFrame(SQL_Query)
conn.close()
return d
