def attendees_last_2_month(file_name):
#update_meetings()
sql = f"""SELECT meeting_date, COUNT(name), COUNT(firstname), COUNT(name) - COUNT(firstname)
FROM (
SELECT DATE(join_time) as meeting_date, type as meeting_type, topic, name, firstname
FROM attendees a
LEFT Join students ON a.user_email = students.email
LEFT Join meetings m ON a.meeting_uuid = m.uuid
WHERE DATE(join_time) > DATE('now', '-2 Month')
GROUP BY meeting_date, name
)
GROUP BY meeting_date
ORDER BY 1"""
comm, curspr = open_db()
rows = exec_query(curspr, sql)
close_db(curspr)
bars1 = []
bars2 = []
names = []
# plot last n lines
for row in rows:
names.append(row[0]) # meeting date
bars1.append(row[1]) # count (first name) --> Academy students
bars2.append(row[3]) # External Students
plot_stacked_bar(bars1, bars2, names, file_name)
def attendees_per_day_of_week(file_name):
#update_meetings()
sql = f"""SELECT strftime('%w',meeting_date) as day_of_week, COUNT(name), COUNT(firstname), COUNT(name) - COUNT(firstname)
FROM (
SELECT DATE(join_time) as meeting_date, name, firstname
FROM attendees a
LEFT Join students ON a.user_email = students.email
LEFT Join meetings m ON a.meeting_uuid = m.uuid
GROUP BY meeting_date, name
)
GROUP BY day_of_week
ORDER BY 1"""
comm, curspr = open_db()
rows = exec_query(curspr, sql)
close_db(curspr)
bars1 = []
bars2 = []
names = [
'Sunday', 'Monday', 'Tuesday', 'Wednesday ', 'Thursday', 'Friday',
'Saturday'
]
# plot last n lines
for row in rows:
#names.append(row[0]) # meeting day
bars1.append(row[1]) # count (first name) --> Academy students
bars2.append(row[3]) # External Students
plot_stacked_bar(bars1, bars2, names, file_name)
def get_page_dict(page_no):
conn, cursor = open_db()
rows = exec_query (cursor, f"SELECT * FROM pdf_book WHERE page_no = {page_no}")
close_db(cursor)
# print (page_no, len (rows[0]))
if len(rows) == 0:
return None
else:
return rows
def query_to_excel(cmd, file_name, header=None):
conn, cursor = open_db()
rows = exec_query(cursor, cmd)
close_db(cursor)
if not header:
header = get_col_names(conn, cmd)
wb = Workbook()
ws = wb.active
ws.append(header)
for row in rows:
ws.append(row)
wb.save(file_name)
def edit_page_text_details(org_txt, cursor, page_no):
# fp = open ('.\\out\\tt.txt', 'a', encoding = 'utf-8')
edited_text = ''
for l in org_txt.split('\n'):
edited_line = ''
for i,w in enumerate(l.split()):
cmd = f"SELECT dict_word FROM pdf_book WHERE pdf_word = '{w}' and page_no = {page_no}"
rows = exec_query (cursor, cmd)
if len(rows) == 0 or len (rows[0]) == 0:
# fp.writelines (cmd)
w_db = '****'
else:
w_db = rows[0][0]
if i > 0 and w_db == '،':
edited_line += w_db # ommit space before comma
else:
edited_line += ' ' + w_db
edited_text += edited_line + '\n'
# fp.close()
return edited_text
def rebuild_page_text(page_no):
conn, cursor = open_db()
rows = exec_query (cursor, f"SELECT page_raw FROM page_text WHERE page_no = {page_no}")
if len(rows) == 0:
tk.messagebox ('Page not found')
close_db(cursor)
return
else:
edited_page = edit_page_text_details(rows[0][0], cursor, page_no)
cmd = f"UPDATE page_text SET page_edited_txt = '{edited_page}' WHERE page_no = {page_no}"
r= exec_db_cmd_conn (cmd, cursor)
if r == 0: #success
update_dict(page_no, conn, cursor)
conn.commit()
close_db(cursor)
return edited_page
def stats_attendees_graph():
sql = f"""SELECT meeting_date, meeting_type, topic, COUNT(name), COUNT(firstname), COUNT(name) - COUNT(firstname)
FROM (
SELECT DATE(join_time) as meeting_date, type as meeting_type, topic, name, firstname
FROM attendees a
LEFT Join students ON a.user_email = students.email
LEFT Join meetings m ON a.meeting_uuid = m.uuid
GROUP BY meeting_date, name
)
GROUP BY meeting_date
ORDER BY 1, 2, 3"""
comm, curspr = open_db()
rows = exec_query(curspr, sql)
close_db(curspr)
bars1 = []
bars2 = []
names = []
for row in rows:
names.append(row[0])
bars1.append(row[4])
bars2.append(row[5])
plot_stacked_bar(bars1, bars2, names, r".\data\attendess by date.png")
def pdf2db(page_no):
page = f".\\data\\books\\ABH\\pages\\ABH-{format(page_no, '03')}.png"
words= str(((pytesseract.image_to_string(Image.open(page), lang='ara'))))
# get last record number in dict
conn, cursor = open_db()
rows = exec_query (cursor, "select count(*) FROM pdf_dict")
if len(rows) == 0:
st_dict_no = 1
else:
st_dict_no = rows[0][0]+1
for w in words.split():
insert_row_list(conn, cursor, 'pdf_dict', [w,w,page_no], ignoreUnique=True)
conn.commit()
close_db(cursor)
words = words.replace("'", "*") # replace single quote
words = words.replace('"', "*") # replace double quote
cmd = f"""INSERT INTO page_text VALUES ({page_no}, '{words}', '', {st_dict_no})"""
exec_db_cmd(cmd)
def update_dict(page_no, conn, cursor):
rows = exec_query (cursor, f"SELECT * FROM pdf_book WHERE page_no = {page_no} and type = 'Dict'")
for row in rows:
insert_row_list(conn, cursor, 'pdf_dict', [row[0],row[1],row[2]], ignoreUnique=True)
def table_exists(table_name, cursor):
cmd = f"SELECT 1 FROM sqlite_master WHERE type = 'table' AND name= '{table_name}'"
row = exec_query(cursor, cmd)
return len(row) # new table