diff --git a/call.cgi b/call.cgi index 1353894..fd9623d 100755 --- a/call.cgi +++ b/call.cgi @@ -1,4 +1,5 @@ #!/usr/bin/env python +# encoding: utf-8 import os import sys import cgi @@ -7,12 +8,12 @@ import pickle from datetime import datetime from datetime import timedelta -#import openpyxl if sys.argv[-1] == "--test": logdir="./" else: logdir = "/var/log/httpd/" + logdir="./" datefmt = "%Y/%m/%d %H:%M" @@ -26,6 +27,7 @@ elif ip[2] == 118: prefix = "ML2" elif ip[2] == 119: prefix = "HSJ" else: return ip_str + if prefix != "HSJ" and 151 <= ip[3] <= 150 + 49 or\ prefix == "HSJ" and 151 <= ip[3] <= 150 + 63: return "%s-%02d" % (prefix, ip[3]-150) @@ -36,11 +38,11 @@ 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") -l = fr + timedelta(7) # a week after -higher = "access_log-%04d%02d%02d"%(l.year, l.month, l.day) +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 and fname <=higher] + if fname.startswith("access_log-") and lower <= fname <=higher] if not logfiles: logfiles.append('access_log') maildb = pickle.load(open("name.pickle")) @@ -48,6 +50,7 @@ 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] @@ -61,10 +64,11 @@ for machine in db.keys(): candidate = [] active = False + reject = False for date, action in db[machine]: if action.startswith("on:"): + action, id_ = action.split(":") if date < to: - _, id_ = action.split(":") if id_ in maildb: date = date.strftime(datefmt) active = True @@ -75,8 +79,16 @@ maildb[id_]["no"], maildb[id_]["name"], # 7 8 maildb[id_]["reading"], maildb[id_]["dep"]]) - if action.startswith("off:") or action.startswith("shutdown"): - if candidate: + elif action.startswith("reject") and candidate: + reject = True + candidate.pop() + 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]: @@ -90,4 +102,82 @@ json.dump(lines, sys.stdout) else: - pass + from openpyxl import load_workbook + from openpyxl.worksheet.table import Table, TableStyleInfo + from openpyxl.styles import Alignment + import tempfile + + wb = load_workbook("./attendancebook_template.xlsx") + + lines.sort(lambda x,y: cmp(x[6],y[6])) + for room in ["GRL", "ML1", "ML2", "HSJ"]: + if room in form: + wb.remove(wb.get_sheet_by_name(room+u"出席表")) + #.remove() + ws = wb.create_sheet(title = room+u"出席表") + extract = [[host, str(student_no),logon[-5:],logoff[-5:], name, reading, dep] + for host, logon, logoff, _, _, + student_no, name, reading, dep in lines if host.startswith(room)] + + ws.column_dimensions["A"].width =9.0; ws.column_dimensions["B"].width =11.0; + ws.column_dimensions["C"].width =6.0; ws.column_dimensions["D"].width =6.0; + ws.column_dimensions["E"].width =15.0; ws.column_dimensions["F"].width =18.0; + ws.column_dimensions["G"].width =7.0; # setup width of each columns + + ws.append(["学生", ]) + ws.append(["ホスト名", "学籍番号", "開始", "終了", "名前", "よみ", "所属"]) + stafflist=[] + prev = None + row = None + align = Alignment(wrap_text=True, vertical="center", horizontal="center") + for e in extract: + if e[6] != "STAFF" and not e[1].startswith("M"): + if prev == e[1]: + row = str(ws.max_row) + ws['C'+row]=ws['C'+row].value + "\n"+e[2] + ws['D'+row]=ws['D'+row].value + "\n"+e[3] + else: + 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 + else: stafflist.append(e) + prev = e[1] + tab = Table(displayName="StudentTable"+room, ref="A2:G"+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'+room+'[学籍番号])', '人']) + 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 + tab = Table(displayName="StaffTable"+room, 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) + + 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) + print "Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" + print 'Content-Disposition: attachment; filename="'+filename+'"' + print 'Content-Length: '+str(finfo.st_size) + print "" + print f.read() +