#!/usr/bin/env python3
# encoding: utf-8
import os
import sys
import cgi
import re
import json
import sqlite3
from datetime import datetime
from datetime import timedelta
import datetime as dt
if sys.argv[-1] == "--test":
logdir="./"
logdir = "/var/log/httpd/"
else:
logdir = "/var/log/httpd/"
datefmt = "%H:%M"
form = cgi.FieldStorage()
def machine_name(ip_str):
ip = [int(v) for v in ip_str.split(".")]
if ip_str.startswith("172.29.11"):
if ip[2] == 116: prefix = "GRL"
elif ip[2] == 117: prefix = "ML1"
elif ip[2] == 118: prefix = "ML2"
elif ip[2] == 119: prefix = "IML"
else: return ip_str
if prefix != "IML" and 151 <= ip[3] <= 150 + 49 or\
prefix == "IML" and 151 <= ip[3] <= 150 + 63:
return "%s-%02d" % (prefix, ip[3]-150)
if prefix != "IML" and 81 <= ip[3] <= 81 + 49 or\
prefix == "IML" and 81 <= ip[3] <= 81 + 63:
return "%s-%02d" % (prefix, ip[3]-80)
if 220 <= ip[3] <= 220 + 5:
return "%s-T%02d" % (prefix, ip[3]-220)
if 226 <= ip[3] <= 226 + 5:
return "%s-T%02d" % (prefix, ip[3]-225)
else: return ip_str
fr = datetime.strptime(form["date"].value + " " + form["from"].value, "%Y/%m/%d %H:%M")
to = datetime.strptime(form["date"].value + " " + form["to"].value, "%Y/%m/%d %H:%M")
tail = fr + timedelta(7) # a week after
lower = "access_log-%04d%02d%02d"%(fr.year, fr.month, fr.day)
higher = "access_log-%04d%02d%02d"%(tail.year, tail.month, tail.day)
logfiles = [fname for fname in os.listdir(logdir)
if fname.startswith("access_log-") and lower <= fname <=higher]
if not logfiles: logfiles.append('access_log')
db = {}
lines = []
for fname in logfiles[-2:]:
with open(logdir + fname) as f:
# filter entries one by one
line = [l for l in f.readlines() if "logonoff" in l]
line = [re.match(r'^([0-9.]*).*\[(\d+/\w+/\d+:\d+:\d+:\d+).*logonoff\?(.*) HTTP', l) for l in line]
line = [l.groups() for l in line if l]
line = [(machine_name(ip), datetime.strptime(date, '%d/%b/%Y:%H:%M:%S'), action)
for ip, date, action in line]
for host, date, action in line:
if host in db:
db[host].append([date,action])
else:
db[host] = [[date,action]]
conn = sqlite3.connect("/var/www/etc/kguid.sqlite3")
c = conn.cursor()
query= "SELECT number, name_kanji, name_kana, depart_name, pos_code FROM user WHERE id = ?"
for machine in list(db.keys()):
candidate = []
active = False
reject = False
for date, action in db[machine]:
if action.startswith("on:"):
if candidate != [] and candidate[-1][2] == "---":
candidate.pop()
action, id_ = action.split(":")
id_ = id_.lower()
if date < to:
date = date.strftime(datefmt)
active = True
res = c.execute(query, (id_,))
dd = res.fetchone()
if dd:
candidate.append(
# 0 1 2 3 4
[machine, date, "---", action, id_,
# 5 6
dd[0], dd[1],
# 7 8
dd[2], dd[3], dd[4], ""])
else:
candidate.append(
# 0 1 2 3 4
[machine, date, "---", action, id_,
# 5 6
"????", id_,
# 7 8
"--", "--", "--", ""])
elif action.startswith("reject") and candidate: # exclude double-login
action, id_, host = action.split(":")
if id_.lower() == candidate[-1][4]:
reject = True
candidate.pop()
elif action.startswith("startup") and candidate:
action = "off:" + candidate[-1][4]
elif action.startswith("shutdown") and candidate:
action = "off:" + candidate[-1][4]
if action.startswith("off:"):
action, id_ = action.split(":")
if reject:
reject=False
elif candidate:
if not fr < date:
candidate.pop()
elif active and id_ == candidate[-1][4]:
active = False
candidate[-1][2] = date.strftime(datefmt)
if candidate[-1][1].startswith("?"):
candidate[-1][1] = "?〜"+date.strftime(datefmt)
else:
if date.time() > dt.time(19,55) and date.time() > dt.time(18,45):
candidate[-1][1] += "〜記録無"
candidate[-1][10] = "ログオフ記録無"
else:
candidate[-1][1] += "〜" + date.strftime(datefmt)
elif fr < date and to > date: # no candidate
to_entry = date.strftime(datefmt)
entry = date.strftime(datefmt)
res = c.execute(query, (id_,))
dd = res.fetchone()
if dd:
candidate.append(
# 0 1 2 3 4
[machine, "?〜" + entry, to_entry, action, id_,
# 5 6
dd[0], dd[1],
# 7 8
dd[2], dd[3], dd[4], ""])
else:
candidate.append(
# 0 1 2 3 4
[machine, "?〜"+entry, "---", action, id_,
# 5 6
"????", id_,
# 7 8
"--", "--", "--", ""])
lines.extend(candidate)
c.close()
conn.close()
if not "excel" in form:
print("Content-Type: application/json")
print("")
json.dump(lines, sys.stdout)
else:
sys.path.append("/home/tkuro/.local/lib/python3.9/site-packages/")
from openpyxl import load_workbook
from openpyxl.worksheet.table import Table, TableStyleInfo
from openpyxl.styles import Alignment
from openpyxl.styles.fonts import Font as xlfont
import tempfile
wb = load_workbook("./attendancebook_template.xlsx")
lines.sort(key=lambda x: x[4]+x[1])
for room in ["GRL", "ML1", "ML2", "IML"]:
wb.remove(wb[room+"出席表"])
if room in form:
ws = wb.create_sheet(title = room+"出席表")
ws.sheet_properties.pageSetUpPr.fitToPage = True
if room == "IML":
prefix = "IML"
table_prefix = "HSJ"
else:
prefix = room
table_prefix = room
extract = [[host, str(student_no),logon[:5],logoff[-5:], name, reading, dep, "", pos]
for host, logon, logoff, _, _,
student_no, name, reading, dep, pos, status in lines if host != None and host.startswith(prefix)]
ws.column_dimensions["A"].width =8.5; ws.column_dimensions["B"].width =10.0
ws.column_dimensions["C"].width =7.0; ws.column_dimensions["D"].width =7.0
ws.column_dimensions["E"].width =16.0; ws.column_dimensions["F"].width =18.0
ws.column_dimensions["G"].width =17.8; ws.column_dimensions["H"].width =17.0
# setup width of each columns
ws.append(["学生", "","","", fr.strftime("%Y/%m/%d"), fr.strftime("%H:%M")+"〜"+ to.strftime("%H:%M")])
ws.append(["ホスト名", "学籍番号", "開始", "終了", "名前", "よみ", "所属", "備考"])
stafflist=[]
prev = None
row = None
align = Alignment(wrap_text=True, vertical="center", horizontal="center")
IMLroomA = [ "01","02","03","04","05","06",
"11","12","13","14","15","16",
"21","22","23","24","25","26",
"31","32","33","34","35","36",
"41","42","43","44",
"51","52","53","54",
"K01","K03","61"];
for e in extract:
login = dt.time.fromisoformat(e[2])
logout = dt.time.fromisoformat(e[3])
font = None
if dt.time(18,45) >= login and logout >= dt.time(19,50):
e[3] = "記録無"
e[7] = "ログオフ記録無"
font = xlfont(color = "b00000")
if room == "IML":
in_rooma = e[0][4:] in IMLroomA
if in_rooma:
if not "IML-A" in form:
continue
else:
if not "IML-B" in form:
continue
if e[8] == "01" and not e[1].startswith("M"):
if prev == e[1]:
row = str(ws.max_row)
ws['A'+row]=e[0]
ws['C'+row]=ws['C'+row].value + "\n"+str(e[2])
ws['D'+row]=ws['D'+row].value + "\n"+e[3]
else:
ws.append(e[:-1])
row = str(ws.max_row)
ws['A'+row].alignment = align; ws['B'+row].alignment = align
ws['C'+row].alignment = align; ws['D'+row].alignment = align
ws['E'+row].alignment = align; ws['F'+row].alignment = align
ws['G'+row].alignment = align; ws['H'+row].alignment = align
# TODO need refactor: maybe it can be integrated
if font:
ws['A'+row].font = font; ws['B'+row].font = font
ws['C'+row].font = font; ws['D'+row].font = font
ws['E'+row].font = font; ws['F'+row].font = font
ws['G'+row].font = font; ws['H'+row].font = font
# TODO need refactor: maybe it can be integrated
else: stafflist.append(e[:-1])
prev = e[1]
tab = Table(displayName="StudentTable"+table_prefix, ref="A2:H"+str(ws.max_row))
style = TableStyleInfo(name="TableStyleLight1", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=False)
tab.tableStyleInfo = style
ws.add_table(tab)
ws.append([])
ws.append(['合計', '=COUNTA(StudentTable'+table_prefix+'[学籍番号])', '人'])
ws.append([])
ws.append(["スタッフ"])
ws.append(["ホスト名", "ユーザ名", "開始", "終了", "名前", "よみ", "所属","ステータス"])
staff_start = ws.max_row
for e in stafflist:
ws.append(e)
row = str(ws.max_row)
ws['A'+row].alignment = align; ws['B'+row].alignment = align
ws['C'+row].alignment = align; ws['D'+row].alignment = align
ws['E'+row].alignment = align; ws['F'+row].alignment = align
ws['G'+row].alignment = align # TODO need refactor: maybe it can be integrated
if ws.max_row == staff_start:
ws.append(["-","-","-","-","-","-","-"])
tab = Table(displayName="StaffTable"+table_prefix, ref="A"+str(staff_start)+":G"+str(ws.max_row))
style = TableStyleInfo(name="TableStyleLight2", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=False)
tab.tableStyleInfo = style
ws.add_table(tab)
ws.column_dimensions['H'].hidden = True
else:
wb.remove(wb[room+"座席表"])
# wb.move_sheet('注意事項', offset=len(wb.sheetnames))
filename="attendancebook"+fr.strftime("%Y%m%d%H%M")+".xlsx"
tname= tempfile.mktemp(filename)
wb.save(tname)
with open(tname,"rb") as f:
finfo = os.stat(tname)
sys.stdout.buffer.write(b"Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet\n")
sys.stdout.buffer.write(('Content-Disposition: attachment; filename="'+filename+'"\n').encode("ascii"))
sys.stdout.buffer.write(('Content-Length: '+str(finfo.st_size)+"\n\n").encode("ascii"))
sys.stdout.buffer.write(f.read())
